|
Hi. I came to software development primarily through physics. I have a fair amount of experience, but not much of it has to do with databases. I'm building an exercise database for a web site, and I need some suggestions about schema.
The idea is to relate exercises to muscles. This, however, is overly simplistic. Any exercise can belong to a group of exercises, and there can be multiple variations of an exercise. For example, front squats and back squats are fundamentally different exercises, even though both are squats. Likewise, when doing a back squat, someone can go down until thighs are parallel, or go down until his rear is almost touching the ground. These are both back squats, but have quite different effects.
My initial thought was to have a table for exercise groups, exercises, and exercise variations. Along the same lines I would have a table for a muscle group, and another for muscles. All would have mutually exclusive unique id's. This way, in a single relational table (many to many relationship), a given row could have one field containing a muscle or a muscle group, and another field can contain an exercise group, and exercise, or an exercise variation.
Although this would be well normalised, I'm thinking that once I carry this on to a few other things (like heads, joints, etc) this could result in a lot of small tables. It is also somewhat limiting. What if I want a variation of a variation, for example?
My second idea was that muscles and muscle groups would be in one table. There would be a field called 'group'. 'vastus lateralis' would be related to 'quadriceps'. For 'quadriceps', this field would be blank. Likewise, in the exercise table, I would include exercises, exercise groups, and exercise variations. Then under the 'variation of' field, 'parallel squat' would be related to 'back squat', 'back squat' would be related to 'squat', and for 'squat' the field would be empty. This seems to me to be a more flexible approach, and would probably be easier to maintain, but like I said, I do not have the benefit of experience on this. Can anyone give me advice on this?
Thanks
Matt Brown
|
|
|
|
|
Hy,
I have something like this:
SELECT Code, SUM(QualityData.NrOfDefects) AS Defects
FROM QualityData
GROUP BY Code
ORDER BY SUM(QualityData.NrOfDefects);
The dilema is that
ORDER BY Defects does not work
and
ORDER BY SUM(QualityData.NrOfDefects);
Is there a better way?
Thanks
|
|
|
|
|
SELECT Code, Defects from
(SELECT Code, SUM(QualityData.NrOfDefects) AS Defects
FROM QualityData
GROUP BY Code)
ORDER BY Defects;
|
|
|
|
|
|
OK. I did not have access to much resources when I answered your question. I assumed that you had problems with your original queries. However, here I have access to my environment, and I tested your query
SELECT Code, SUM(QualityData.NrOfDefects) AS Defects
FROM QualityData
GROUP BY Code
ORDER BY SUM(QualityData.NrOfDefects);
It works with no problem. It works with no problem if I use
ORDER BY Defects
So just ignore my "solution". There must be something else in your problem.
My test environment is MySQL version 5.
|
|
|
|
|
It's all fixed now.
I now know for a fact(tested) that even the
ORDER BY SUM(QualityData.NrOfDefects);
does not cause a recalculation.
|
|
|
|
|
Your initial query is fine; it will not require a second set of calculations for the sort. To verify this, run the query in Management Studio and look at the Actual Execution Plan.
|
|
|
|
|
Hello guys.....title says it all. How can we make a database of music and access it using vb.net or C#.net??
|
|
|
|
|
Buy a Database book or copy one of these:
Google[^]I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
This is not what I was talking about. Lets suppose that i wanna make my own media player. In this case, I will need to access the music from my own hard disk and make a playlist.....
|
|
|
|
|
Muzammil Saeed wrote: This is not what I was talking about.
Your title says "Music Database", and your first entry states "Hello guys.....title says it all.", so which is it to be? If you have no interest in creating a music database then try and make your question clearer.
To make a playlist you need to scan all your music files, extracting the title, composer, orchestra etc information and building some sort of database which your media player can access. If you think a database is too much work you can use any alternative file type that you feel comfortable with.txtspeak is the realm of 9 year old children, not developers. Christian Graus
|
|
|
|
|
You did not talk about anything - you were hoping that someone would give you their work without you lifting a finger.
This is an easily found site that will do half of the work for you, if you can find the library you need to work from:
Database Models[^]
Edit you can also follow the instructions here: MSDN[^]I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
modified on Saturday, February 13, 2010 3:53 PM
|
|
|
|
|
It's an Useful site Andy. Thank you.
|
|
|
|
|
Thanx for sharing info about MSDN link....but i dont know why poeple over-react. I did not meant that someone will give his knowledge to me,without my finger raised ....and stuff like that you wrote. If person A asks person B...it already means that person B might have more knowledge than person A, and showing respect to person B. Can I do anything to your knowledge except asking you? The only option remains is not to ask from you but from someone else.
|
|
|
|
|
It is good form to show what you have tried, or even describe the solution that you "think" may be along the right lines. Unfortunately, you did neither. Therefore it is difficult for anyone to answer your question - we are not mind readers. (My first reply to you was to useless information because that is what I had to go on.)
If you had said in the op that you had looked at various sites and MSDN then the response would have been warmer.
It did appear however that you did neither.I don't speak Idiot - please talk slowly and clearly
'This space for rent'
Driven to the arms of Heineken by the wife
|
|
|
|
|
Which database are you speaking of?
I can think of two options:
1. Saving the path of the music files in the database and then loading the files in the player from that path.
2. Saving the file as a Blob in the database and reading and playing them through your app."No matter how many fish in the sea; it will be so empty without me." - From song "Without me" by Eminem
|
|
|
|
|
suppose i have few fileds in table and one filed is xml type. how to search this.
the xml which is stored in my xml filed like
<OpenShipments xmlns="x-schema:C:\UPSLabel\OpenShipments.xdr">
<OpenShipment ShipmentOption="RS" ProcessStatus="">
<ShipTo>
<CompanyOrName>CRESCENT AUTO SERVICES LTD</CompanyOrName>
<Attention>ZULFI</Attention>
<Address1>16-18 THE CRESCENT</Address1>
<Address2 />
<Address3>SOUTHALL</Address3>
<CountryTerritory>GB</CountryTerritory>
<PostalCode>UB1 1BE</PostalCode>
<CityOrTown>SOUTHALL</CityOrTown>
<StateProvinceCounty>SOUTHALL</StateProvinceCounty>
<Telephone>02085719998/02085742845</Telephone>
</ShipTo>
<ShipmentInformation>
<ServiceType>UPS Standard</ServiceType>
<PackageType />
<NumberOfPackages>1</NumberOfPackages>
<ShipmentActualWeight>5</ShipmentActualWeight>
<DescriptionOfGoods>Car Parts</DescriptionOfGoods>
<BillingOption>PP</BillingOption>
<ReturnService>
<Options>PRL</Options>
<MerchandiseDescOfPackage>OLD CORE RETURN</MerchandiseDescOfPackage>
</ReturnService>
</ShipmentInformation>
</OpenShipment>
</OpenShipments>
we can query sql server data very easily but i dont know how to query the xml data which stored in xml filed.
how to query address field in my xml or how to query Options field,how query CompanyOrName.
please help me to construct the sql. guide me in detail with few sample for querying xml datatype.
thanks in advancetbhattacharjee
|
|
|
|
|
|
Tridip Bhattacharjee wrote: how to query address field in my xml or how to query Options field,how query CompanyOrName.
A XML-query will look somewhat more complicated then a "regular" query. I hope that the example below provides a good impression of the used construct;
DECLARE @ShipXML XML
SET @ShipXML =
'<OpenShipments>
<OpenShipment>
<NumberOfPackages>1</NumberOfPackages>
<ShipmentActualWeight>5</ShipmentActualWeight>
<DescriptionOfGoods>Car Parts</DescriptionOfGoods>
</OpenShipment>
<OpenShipment>
<NumberOfPackages>2</NumberOfPackages>
<ShipmentActualWeight>3</ShipmentActualWeight>
<DescriptionOfGoods>Bike Parts</DescriptionOfGoods>
</OpenShipment>
</OpenShipments>'
SELECT shs.sh.value('NumberOfPackages[1]','bigint') AS NumberOfPackages
,shs.sh.value('ShipmentActualWeight[1]','bigint') AS ShipmentActualWeight
,shs.sh.value('DescriptionOfGoods[1]','varchar(20)') AS DescriptionOfGoods
FROM @ShipXML.nodes('OpenShipments/OpenShipment') shs(sh)
WHERE shs.sh.exist('.[DescriptionOfGoods != "Car Parts"]') = 1 The example was built using sample-code from MSDN[^].I are Troll
|
|
|
|
|
We currently have a Crystal Report that uses a SQL 2005 database as it's datasource.
Both start date and end date are the only two parameters that are sent to stored procedure. These dates can vary - meaning it's not always MTD or YTD.
The user would like the report details to be exported to Excel.
I've used DTS in the past, but haven't had an opportunity to play with SSRS or SSIS.
Which should I focus on for this task?
We do have a Sharepoint portal and ideally, the process can start there by clicking a link.
How would the parameters be inputted by the user?
Thanks in advance.
|
|
|
|
|
One of the (minor) reasons we dumped (on) Crystal was the export to excel was so crappy.
We use SSRS and the excel export is one hell of a lot better than Crystal used to be. Having said that SSIS does not support data dumps, it shouldn't it is a REPORTING tool. I repeat this line to my management weekly, it does not help. They still insist that data dumps are a requirement.
So one of our devs has extended SSRS to export csv files via RS. This is not possible in CR and is pretty ugly even in SSRS, it means hacking the UI to a custom page that calls a stored proc that writes the file to a folder and then displays a link for the user to download the file. As I said ugly, but it works without having to pull major volume to the UI to export.Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi
I Execute the following sql statement successfully for a Access Database.
CREATE TABLE Scores (
StudentCode Text(9) NOT NULL,
CourseCode Text(7) NOT NULL,
SemesterCode Text(3) NOT NULL,
Score Double
);
CREATE TABLE Courses (
CourseCode Text(7) NOT NULL,
CourseName Memo NOT NULL,
CourseTypeID Integer NOT NULL,
CourseCategoryID Integer NOT NULL
);
ALTER TABLE Scores ADD CONSTRAINT PK_Grades
PRIMARY KEY (StudentCode, CourseCode, SemesterCode);
ALTER TABLE Courses ADD CONSTRAINT PK_Courses
PRIMARY KEY (CourseCode);
ALTER TABLE Courses
ADD CONSTRAINT UQ_Courses_CourseCode UNIQUE (CourseCode);
But Finally I Get Error message(Syntax error in CONSTRAINT clause.) on this satement that is at the end of my execution list.
ALTER TABLE Scores ADD CONSTRAINT FK_Scores_Courses
FOREIGN KEY (CourseCode) REFERENCES Courses (CourseCode)
ON UPDATE CASCADE;
And I don't know Why?
Please Help me on how to establish a relation in access database through sql statement?
|
|
|
|
|
hi,
i wrote msAccess query like.
SELECT T1.[CID],T2.[Name]
FROM T1 INNER JOIN T2 ON T1.TID =T2.TID
WHERE T1.CID in ('select CID from T3')
here i'm getting error :
"Cannot join on memo,ole,or hyperlink object (T1.TID =T2.TID )"
please send solution for this
Thanks & Regards,
Vishnu.
|
|
|
|
|
So one of your fields is in this data format, check each field in the join and change the data type of the offending field. Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I need to query a csv file with C++ .NET and get a distinct list which is ordered by multiple criterion. If I write regular SQL, then i get the error "order by clause conflicts with distinct". If I use the prefix "SELECT * FROM ( SELECT" and suffix ")", then the order is lost.
My current workaround is to manually modify the results to a distinct set using C++.modified on Wednesday, February 10, 2010 5:24 AM
|
|
|
|