I'm just now learning both SQL and ASP.NET, and I cannot seem to figure out how to build my data structure. I believe the answer to my problem is a composite key, but I cannot seem to get it to work. Here is an example. My database is of recorded dances, with exact locations within a ballroom. I believe I need 2 tables
Table #1 - DanceTable
Columns: DanceID, Name, Description, Tags
Table #2 - StepsTable
Columns DanceID, StepID, longLocation, latLocation, Action, Description
Within my ASP.NET application I want to be able to enter data about a dance, including metadata and a series of steps. The Dance and metadata content to be stored in DanceTable, and the series of moves stored in the StepsTable. I want the steps to be IDed as 1, 2, 3, 4...x with the first step being labled 1. and I want each dance to have it's own unique ID (DanceID). Right now I'm using "ExecuteNonQuery()" to add my data to my SQL database, and when I add new steps to the StepsTable SQL just finds the largest ID within StepID and increments it by one. So my steps are labeled as:
Dance1:
Step1, Step2, Step3, Step4
Dance2:
Step5, Step 6, Step7
What I really want is (or I think what I want is) is a composite primary key.
Dance1:
Step1, Step2, Step3, Step4
Dance2:
Step1, Step2, Step3
That way the StepID is used as both a primary key and it indicates the position within the dance. I guess I could just use a standard SQL table, let SQL auto generate StepID's and then add a new column called something like "StepNumber", but it just seems goofy to be generating a stepID and never using it. With composite keys (If I understand them) each step would have a unique key as a combination of the DanceID+StepID (AKA Dance 345, steps 1-10).
I pull up data by searching for dances, and then sort by StepNumber, and those should all be unique...if I can figure out how to build them.
A composite key is just a key made from multiple fields. In your case, it would be DanceID,StepID.
Your tables look fine, although if you make the StepID an identity field, you really don't need to store the "StepNumber". It's redundant. You can derive the "StepNumber" by the number of records that have the same DanceID and a lower StepID.
This would get you the @.StepNumber-th step:
SELECT TOP 1 *
FROM ( SELECT TOP (@.StepNumber) *
FROM StepsTable
WHEREDanceID=@.DanceID
ORDER BY StepID ASC) t1
ORDER BY t1.StepID DESC
Or get them all in order with "StepNumber":
SELECT *,ROW_NUMBER() OVER (ORDER BY StepID) As StepNumber
FROM StepsTable
WHEREDanceID=@.DanceID
ORDER BY StepID
But of course there is nothing prohibiting you from including a StepNumber field (Like in case you don't always want the steps to be renumbered, or they aren't contiguious, like step 1, 3, 5 with no step 2 or 4, etc). In this case, your primary key would be StepID, and I would create a unique index/constraint on DanceID,StepNumber.
