-2

I have a table of exercises. I want to be able to create a workout in my react app and then submit it to the database. but I'm struggling to think how the schema would work. how can I achieve this?

I essentially want a workout to be an array of exercises. But I am aware storing arrays in databases isn't the best idea

But I can't understand how if I had a table of exercises and then a table of workouts, how I can join them together to make a table where I have a list of workouts with exercises

I want to be able to pull out a unique workout and that will give me the list of exercises from that workout along with a date and other info.

The only way I can picture it is like:

id: 1, name: 'my first workout', exercises: [array of objects]

Any ideas how this schema would look?

Kalamarico
  • 5,466
  • 22
  • 53
  • 70
Red Baron
  • 7,181
  • 10
  • 39
  • 86
  • 2
    Edit title to be specific to your particular question. – Basil Bourque Mar 17 '19 at 16:35
  • Time to read a published academic textbook on information modeling & database design. (Manuals for languages & tools to record & use designs are not textbooks on doing information modeling & database design.) PS This is (obviously) a(n easily googled) faq. Before considering posting please always google many clear, concise & precise phrasings of your question/problem/goal and/or error message, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. PS Please tag with DBMS or kind of DBMS. – philipxy Mar 18 '19 at 01:51
  • Possible duplicate of [What's the best way to store an array in a relational database?](https://stackoverflow.com/questions/36678545/whats-the-best-way-to-store-an-array-in-a-relational-database) – philipxy Mar 18 '19 at 02:02

1 Answers1

0

For many-to-many relationships, you should have a third table, which stores the relationships, and is called a junction table. For example in your case, each row would have an exerciseID and a workoutID, which means that two (exercise and workout) are related.

So for example there can be many rows in that table with exerciseID = 1 (different workoutIDs) and many rows with workoutID = 5 (different exerciseIDs), hence many-to-many.

More explanation

Each row of the junction table has three fields:

  • Its own id (because it's good practice in database that each table has an id, no matter what)
  • ExerciseID
  • WorkoutID

So for example say we have a junction table like this:

ID      ExerciseID    WorkoutID
-------------------------------
1000    12            5
1001    12            6
1002    12            9
1003    12            12
1004    12            27
1005    13            2
1006    13            6
1007    13            9

which means that:

  • exercise number 12 has workouts [5, 6, 9, 12, 27]
  • exercise number 13 has workouts [2, 6, 9]

But as for how this table should be created:

Either you have a user interface that allows your users to add, edit, and delete exercises and workouts, or you do it your self. Either way, anyone that is defining an exercise or workout gotta know how they are related! So they can also define that relation as a row in the junction table

Amir
  • 1,885
  • 3
  • 19
  • 36
  • ok I get that but I don't understand how that third table would know which workoutId to match with what exercise Id? if that makes sense – Red Baron Mar 17 '19 at 17:09
  • each row of the junction table has three fields: its own `id` (because it's good practice in database that each table has an id, no matter what), `exerciseID` and `workoutID`. So for example one row would be [1000, 12, 5] which means that the exercise number 12 is related to workout number 5 – Amir Mar 17 '19 at 17:12
  • but what I mean is this. say I have `id: 1, name: bicep curls`. in the exercises table. how do I know if that has workout id:1, id: 5 or both or neither? for example – Red Baron Mar 17 '19 at 17:16
  • sorry I'm being really dense. I get that's what it would look like, but I still don't how you got to that table? say exercises has 5 rows, unique ids. and workout table has 5 rows, unique ids. I still am not sure at what point the data set can know. there is nothing linking the 2 tables ( I know the middle table but I cannot see how you arrived at that table or what you did to generate it..) sorry I'm probably being thick – Red Baron Mar 17 '19 at 17:24
  • Well of course the data itself cannot know! But you know, and you populate the table. The common scenario is that when you are defining each exercise and adding it to the database, you also define its relations (meaning that you add records to junction table). – Amir Mar 17 '19 at 17:28
  • ok haha. so when I make an INSERT INTO statement and I add to my database, I need to add the new workout row in AND I also need to insert into the joining table all the relevant rows for the newly submitted workout along with all it's exercises? – Red Baron Mar 17 '19 at 17:43
  • @RedBaron yes, exactly – Amir Mar 17 '19 at 17:47