Database Design Problem

[hauptra]

I'm a little rusty as far as database design goes. So here is what I am trying to do.

I would like to create a database that tracks workouts at the gym. I wanted to have a number of users who workout enter their workouts. A workout would consist of a number of exercises and the exercises consist of 1 or more number of sets (amount and duration).

Here is what I thought my database would consist of

• A Table for the users
• A Table for the workouts. Each workout points to a user
• A Table for the exercises.
• A Bridge that connects the Workouts to Exercises.
• A Table for the sets
• A Bridge that connects the exercises to sets

I figure that there is probably a better way to represent this in a database. Anyone want to give me a better way to handle this?

Thanks,

Rob

 

[Canopy High]

The primary key of the Users table will be the foreign key in your Workouts table, then the primary key of your Exercises table will also become a foreign key in your Workouts table. Lastly, the primary key of your Sets table will become the foreign key in your Exercises table. You can set this in SQL Server in the Diagrams folder. Hope I helped you.

 

[hauptra]

ok, but here is my question with that. I can have any number of Sets 1, 3 or whatever. How can I link a variable number of sets to a particular Exercise. I figured I would have a table which matched them up.

It seemed to me that this was a Many-To-Many relationship that had to be decomposed into two One-To-Many relationships.

Am I wrong in this thinking?

Rob

 

[Canopy High]

How can I link a variable number of sets to a particular Exercise.

Just use the primary key of your Sets table as a foreign key in your Exercises table.

for example:

Exercises Table
ExerID SetID ExerciseName
------ ----- -------------
     1     1 Bench Press
     2     2 Dead Lift

SetID NumOfSets
----- -----------
    1  12
    2  20

ExerID is your Primary Key. SetID is your Foreign key. Just change the NumOfSets field according to your preference. You can also assign the same SetID to different Exercises.

Hope this one helps.

 

[Arg81]

The only issue with that is if Bench Press is in the Exercise Table and needs to be used for 3 different sets.
i.e.

Exercises Table
ExerID SetID ExerciseName
------ ----- -------------
     1     1 Bench Press
     1     2 Bench Press

In theory you have the same ExerID (which you can't) but a choice of 2 sets.

I would do as you first thought.
Personally I'd setup like;

Exercise(ExerciseID, ExerciseName, ExerciseNotes)

Sets(SetID, SetNo, setNotes)

Workout(WorkoutID, ExerciseID, SetID)


^^ Workout links your two tables together, because like you say, many Exercises can have many Sets.

Now you need to create a WorkOutProgramme. This is slightly different, as you would create the table using 2 Primary Keys, as you want the main ID to always be the same for that programme.

This all depends on what data you need to record, because you want to normalise the data as much as possible yet still try to keep things simple.

lets say you want to give the programme a name, and add notes to that programme. If this is the case, you need to use 2 tables.

ProgrammeDetails(ProgrammeID, WorkoutName, WorkoutNotes)

WorkoutProgramme(WorkoutProgrammeID, WorkOutID, ProgrammeID)

^^ that second table creates your workout programme (as stated!) but 2 fields are Primary Keys. This allows you to have lots of WorkOutIDs to the same WorkoutProgrammeID to create your "group" workout.



Now that you have your tables set up, you can start entering data and creating your application form.

In theory you would have a page that shows:

GymMember details

The Workoutprogramme they have been given, which shows the name and any notes to that programme.

A grid that then shows what exercises are in that programme, and how many sets should be used.

In the table design above, I kind of added a few columns, such as "notes". this means you can control your programmes, by having a set heavy workout that has the note "Ideal for muscle mass".

You can then do a number of queries on this. You could add another column for WorkOutType (weight loss, muscle gain, fitness etc) and from here, list the programmes that are ideal for that workout, or show all members who are currently using that programme.


Database design can get complicated, and sometimes there are times when you could say you only need the one table instead of two. (such as the ProgrammeDetails and WorkOutProgramme) - if you don't need to record the name or notes for a programme, you simply would not have that ProgrammeDetails table. Normalisation is the key, and it always comes down to knowing what exactly you want to record in terms of information. The way I do things is create the extra tables, because you're garunteed at some point someone will want that information, and it's a lot harder to try and adjust your current system to be normalised correctly than it is to simply enter the information in a table that is already there and related!

I hope thats of some help (it feels like I've just wrote an essay!)