Rewbert Rewbert - 2 years ago 59
SQL Question

Design SQL tables with a list behaviour

I have one table

Client (

Another table;

Exercise (

And a final table;

Workout (
creator INT REFERENCES Client(id),
exercise INT REFERENCES Exercise(id),
PRIMARY KEY(name,creator,exercise) );

My tables has some other columns aswell but these are all the relevant ones.
A workout has a creator, it is given a name (for example 'legs - monday' or whatever) and then it has a list of unique exercises one is to perform during this workout. So in an Object oriented approach this would look something like

Object Workout
string name
int creator
List<Exercise> exercises

So far this has worked well for me but now i need to create a new table that has a reference to a workout, and i am not sure how to capture this. Maybe my implementation of this is wrong?

table A (
workout References Workout(?) );

I cannot reference (name,creator) since it is not marked as unique, and even if it was i would only be able to add one exercise then. It seems silly to reference all rows in Workout (my teacher in relational databases would skin me alive).

Would it be bad practice to just reference one arbitrary entry to a workout (name,creator,exercise)? If i have the name and the creator i can just make a select statement and get all the exercises from them. All rows where the tuple (name,creator) match are said to belong to the same workout.

Answer Source

Children should reference a parent, not vice versa:

create table client (
    id serial primary key

create table workout (
    id serial primary key,
    name varchar,
    creator int references client(id)

create table exercise (
    id serial primary key,
    workout int references workout(id)

Then your list view for a given workout may look like this:

select *
from workout w
left join exercise e on e.workout =
where name = 'some name';

Edit. In case of many-to-many relationship the common way is to create so-called join table, e.g.:

create table workout_parts (
    workout int references workout(id),
    exercise int references exercise(id),
    primary key (workout, exercise)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download