CODEWITHSUNDEEP
mysqldatabaseerd
therealtsuki
therealtsuki

Reputation: 101

Can I have several similar foreign keys in one table?

I am developing a management system for a fitness gym. I couldn't get to identify how to visualize my workout plans in a DB. I have this idea (see image below) where I have a plan table and an exercise table.

enter image description here

The idea here is that in every plan, there are 21 exercises (9 for upper, 9 for lower, and 3 for abdominals). This is because a plan has 3 days, and for each day is a different 3-exercise plan for the upper and lower body and a different abdominal exercise each day. Am I doing this right by putting 21 foreign keys of the same exercise ID into the plan table? And if I am doing this right, how do I connect this in an ER diagram? Do I have to connect each foreign key or do I just have to put one cardinality between the two tables?

Upvotes: 0

Views: 95

Answers (1)

Brian Ogden
Brian Ogden

Reputation: 19232

No you are not doing it right, you are missing a table from your database model.

Do this:

Plan -> Plan_Exercises -> Exercises

  • Plan_Exercises table has a Plan_Id column with a Foreign Key constraint to the Primary Key Id column on Plan table
  • Plan_Exercises table has a Exercise_Id column with a Foreign Key constraint to the Primary Key Id column on Exercise table

Plan needs a one to many relationship to exercises, adding the "middleman" PLan_Exercises table provides this relationship.

Upvotes: 1

Related Questions