I have two entity's - Workout and Exercise. These will have a 'many-to-many' (M:N) relationship through the junction table WorkoutExercise. Things start to get complicated when I need to have a 'one-to-many' (1:M) relationship on this table from the table called Sets.

I have provided the entity-relationship diagram below to help illustrate the scenario:

er diagram

Is this poor design? i.e. is it bad for performance? is there a better way?

  • What data would Sets contain that could not be added directly to the junction table?
    – TommCatt
    Sep 16 '15 at 0:43
  • Information specific that particular set such as max/min rest, max/min reps and a comment.
    – mrmustard
    Sep 17 '15 at 13:28
  • Again, why can't that listed information be contained directly in the junction table? What is the purpose of the Sets table?
    – TommCatt
    Sep 17 '15 at 19:49
  • Because there are multiple sets all having different information?
    – mrmustard
    Sep 18 '15 at 18:30

You can build a unique/primary key in the Workout Exercise table that can be referenced by Sets. That will maintain referential integrity and minimize redundancy, assuming that the surrogate key made up of key values from workout and exercise is unique, and you can create one primary key on sets that has that foreign key referential integrity to the associative (junction) table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.