what is the best database design for this table when you have two types of records

Question

i am tracking exercises. i have a workout table with

• id
• exercise_id (foreign key into exercise table)

now, some exercises like weight training would have the fields: weight, reps (i just lifted 10 times @ 100 lbs.)

and other exercises like running would have the fields: time, distance (i just ran 5 miles and it took 1 hours)

should i store these all in the same table and just have some records have 2 fields filled in and the other fields blank or should this be broken down into multiple tables.

at the end of the day, i want to query for all exercises in a day (which will include both types of exercises) so i will have to have some "switch" somewhere to differentiate the different types of exercises

what is the best database design for this situation

Answer 1

if you plan on keeping it only 2 types, just have exercise_id, value1, value2, type

you can filter the type of exercise in the where clause and alias the column names in the same statment so that the results don't say value1 and value2, but weight and reps or time and distance

Answer 2

One way to do it is to have an "exercise" table with a "type" field that names another table where the exercise-specific details are, and a foreign key into that table.

Answer 3

There are a few different patterns for modelling object oriented inheritance in database tables. The most simple being Single table inheritance, which will probably work great in this case.

Implementing it is mostly according to your own suggestion to have some fields filled in and the others blank.