I am building a fitness app and I'm trying to figure out the optimal way to store the data is a MySQL database.
I would like to have specific programs (for example, 'get jacked'), I would like a users to be able to complete a program as many times as they like and log their personal results.
Here our my thoughts on how to do this: Create the following tables to take care of the user/program relationship.
Tables:
**User**
id
programEnrolment[]
**ProgramEnrolment** (this would be the joining table between the users and the program, creating a many to many relationship)
id
userId
programId
**Program**
id
block[]
**Block**
id
Week[]
programId (FK)
**Week**
id
week[]
blockId (FK)
**Workout**
id
set[]
weekId (FK)
**Set**
id
workoutId (FK)
exerciseId (FK)
**Exercise**
id
set[]
Please note a block is a number of weeks, for example a block could be 4 weeks in total and a workout could be made up of 3 blocks, making it 12 weeks in total.
The reason I have included blocks and weeks separately, rather than just adding them as a column in the Workouts table is because on the frontend I want to be able to monitor and display when a block or week is completed and also so I can display workouts by week.
Now this is where I get really confused. I want the user to be able to log their personal results, so my thoughts were to replicate the above table with the prefix user, for example UserProgram, UserWorkout etc. Then I can link the UserProgram to the the Program table. Should this also link to the ProgramEnrolment Table?
