Specific Database Structuring For Fitness Tracking

Question

I've been trying to wrap my head around how exactly this database should be modeled. I'm using Ruby on Rails to do a fitness tracking site. What I want is for users to be able to maintain a list of Exercises that they can then use to build and maintain their list of Routines. Then when they actually complete that routine it would make a Workout which would record the information for that one time they did the Routine.

So, the Exercises and Routines are more like templates that a user can use so they don't have to re-enter all the information every time they do it. Where I'm getting confused is how to put this all together. A Routine can have a variable number of Exercises so I know I would need another table just to hold the association between them. My question is how would I be able to record the information each time in the Workout table? I hope this question is clear and below is my current idea for how to model this setup.

Exercise
type:string (will be limited to "strength" or "cardio")
reps:int
weight:float
sets:int
distance:float
totalTime:time


Routine
title:string


Workout
dateTime:dateTime
???

I'm not sure how the recording of actual workout data can go. I can build the templates all day, but the rest eludes me. Any help is appreciated.

Answer 1

[Disclaimer: I'm not familiar with Ruby on Rails, so this answer is purely from the database modeling perspective.]

Looks like you need a database structure similar to this:

• The diagram above only shows the crucial fields for modeling the relationships between tables. Add "meat" on this "skeleton" as required.

• There is a many-to-many relationship between ROUTINE and EXERCISE, so several routines can contain the same exercise (and vice-verse). The model currently allows for exercises to be shared among different users as well - let me know if that's not desirable.

• The ROUTINE_EXERCISE.EXERCISE_NO defines the order of exercises within the given routine. At the same time, ROUTINE_EXERCISE.EXERCISE_ID is not within primary key, allowing for the same exercise to be repeated at multiple "places" within the same routine.

• Routines are currently "private" to the user. If they should be shareable among the users, there would need to be an additional junction table between USER and ROUTINE, and the restriction on who can workout a given routine described below would need to be changed.

• The ROUTINE uses an identifying relationship (ROUTINE.USER_ID is both FK and part of the PK), so when WORKOUT references USER and ROUTINE, it can migrate USER_ID from both of them. This ensures a user cannot do a workout of a routine that belongs to a different user.

• The ROUTINE.ROUTINE_NAME is a human-readable name of the routine, that is unique at the level of the given user, but different users can "reuse" the same name. You could use something like an integer ROUTINE_NO instead, to keep the referencing tables slimmer.

• The WORKOUT's primary key is useful for clustering - essentially, all workouts belonging to the same user will be stored physically close together in the database. Presumably, the WORKOUT table is the one that will grow the most, so having everything nicely "pre-grouped" (by user) and "pre-sorted" (by date/time, for the given user) can benefit performance.

• The WORKOUT.ROUTINE_NAME is NULL-able, so when user changes or deletes a routine, the WORKOUT can be disconnected from it, without being deleted itself. The alternative would be to never modify or delete the ROUTINE, and instead use some form of versioning (for modifications) and a DELETED flag (for deletions). Something along these lines, but simpler.