Looking for some help on how I might design a database for scoring.
The database is to hold exercises. Each exercise is recorded in a different way - eg. running is stored as distance and time, weights would be stored as weight and repetitions.
I have the tables as shown below but I’ve just got the scores (distance, time, weight, reps) stored in 4 available columns in the logs table. I know there must be a better way to do this. Anyone have any ideas?
What I feel is wrong with the design is that without any log created there is no way of knowing which type of measurement is required for a given exercise. So ideally I would have a way of knowing that when a log is to be created for an exercise the chosen exercise requires distance and time to be set but does not require weight or reps to be set.
but second, and more importantly, your front end app is probably going to have to have some of these smarts built into it, in order to perform consistency edits on the form fields that the users will be entering their exercise data into
in my opinion you can proceed with the current design, and just decide in your front end logic which log type you’re creating
I’d go for separate log tables for each type of exercise. The log datatypes are many and fundamentally different, and so you can’t pull any type conversion strategy.
The database is to hold exercises.
So create the EXERCISES table.
Each exercise is recorded in a different way - eg. running is stored as distance and time, weights would be stored as weight and repetitions.
So create the RUNNING_LOGS and WEIGHTS_LOGS tables.
So far, there is no connection. We create a lookup table: WORKOUTS.
For each new exercise session:
get the exercise id from EXERCISES
create a new workout in the WORKOUTS table for it, with proper id data retrieved above
create a new log in the proper log table: RUNNING or WEIGHTS, with the workout foreign key from WORKOUTS tables, that links back to EXERCISES table
I try to stay away from splitting tables if it can be done in one. I agree with r937, and your original design. It will allow for more ease of reporting if you had any need to build counts of types of exercises / frequencies, etc.