I am making an android app (my first) to record gym workouts but I am not sure on how to best structure the database for storing this data.
I want to be able to store:
-
A list of exercises and their properties
-
A list of workouts and their properties (containing exercises)
-
A list of logged workouts (workout and date of workout)
-
All the set data from each logged workout (exercise, number of sets, weight of each set, reps of each set)
I have drawn up the following schema for how I thought this could be implemented:
My main questions are:
-
Is it optimal to store all the individual set log data in a single table (Log Entries)?
-
Should the Exercise Type and Equipment tables be separate or just columns in the Exercise Table? Same goes for Workout Type
-
Would this be a good approach in terms of performance/usability?
Thanks!
In all likeliehood yes, the exception would be if the number of rows grew to adversely impact response times.
They should be separate to reduce unnecessary duplication and issue e.g. if you were to change a type or and equipment then you’d have to apply that change to all rows in the exercise table with a separate table you just have to make the one change.
With the exception of the Log table (as it stands including wanting the date) then it appears to be a good and efficient approach. The Log table isn’t needed as the Log Entries table has a column for the date time (So in response to the comment yes it would be better to make use of the date/time in the Log Entries and to do away with the log table).