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?