1

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?

2
  • 2
    I think this is an answerable question, but the title is a bit vague. The last paragraph really appears to be your question. Maybe change the title to match the last paragraph? Jan 14 at 13:38
  • thanks @GregBurghardt, I've changed the question.
    – adherb
    Jan 15 at 0:20

0

Your Answer

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

Browse other questions tagged or ask your own question.