Designing a tracking system for ordered workouts

database-design

I've taken a handful of introductory database courses, so I thought I'd design a relational database to help me keep track of my workout routines. It turned out to be a more difficult task than I'd anticipated.

So say I record my workout session with a pen and paper something like this:

Monday

 1. jogging | 1.5 miles
 2. plank   | 3 minutes
 3. lift_A  | 5 pounds  | 15 reps
 4. lift_A  | 5 pounds  | 15 reps
 5. lift_A  | 6 pounds  | 17 reps

Tuesday

 1. plank    | 3 minutes | 3 pounds
 2. wall sit | 2 minutes
 3. jogging  | 1.6 miles
 4. lift_B   | 10 pounds | 2 reps

I need to keep track of the date of each series of exercises as well as the order I do them in. I had trouble figuring out how best to store the three "types" of activities (distance, duration w/ optional weight, weight+reps) and how to manage their recorded order in a day's workout.

How can I design a database (with or without an entity-relationship diagram, which would be nice) to help me record such information? What is the most logical/intuitive approach? Is a relational database even an appropriate approach at all?

Best Answer

Well, there's a simple 1 table solution right here:

| exercise_type (not null) | amount (not null) | other_field (null) | date (not null) |

To get the exercises for a particular date, you'd run a simple select statement (psuedo code): 

select * from exercises where date in between (start, finish)

And you can refine to particular exercise types by adding:

and exercise_type = 'plank'
Related Question