Database issue, how to store changing data structure

Question

Hi there,

I'm building some application, the involves training programs. my issue is like this,

A workout could be simple as this:

3 sets of 45 push ups.

so I would just create 2 fields, sets / count

BUT a workout could be also:

45 minutes run, 3 sets of 45 pushups, 2 minutes of rope jumping, 150 meter swimming.

so i need to build one table, that would know to store the data as it changes it structure, and later I could still translate it to real data on the gui.

how can i make it efficiently and wisely ?

edit:

To make it a bit clear, i want to specify to each workout what Ive done in it. so one workout could be : 3 sets, first: 45 push ups second: 32 push ups third: 30 push ups

and another workout could be: 3 sets of pushups: first: 45 push ups second:32 push ups third: 30 push ups and also 2 minutes of jumping rope 150 meter swimming

the data isn't consistence, one set could be a number of push ups, the next could be a time length etc..

Answer 1

You could create a table with following columns: WorkoutType | Sets | Value | ValueType . So you can store like

----------------------------------
WorkoutType | Sets | Value | ValueType
----------------------------------

Pushups      | 3    | 45   | nos
Run          | null | 45   | minutes
Rope Jumping | null | 2    | minutes 
Swimming     | null | 150  | meter 

Answer 2

You may want to consider a database schema such as the following:

CREATE TABLE workouts (
   workout_id  int,
   user_id     int,
   PRIMARY KEY (workout_id)
) ENGINE=INNODB;

CREATE TABLE sessions_pushups (
   started     datetime,
   workout_id  int,
   number      int,
   PRIMARY KEY (started, workout_id),
   FOREIGN KEY (workout_id) REFERENCES workouts (workout_id)
) ENGINE=INNODB;

CREATE TABLE sessions_rope_jumping (
   started          datetime,
   workout_id       int,
   duration_minutes int,
   PRIMARY KEY (started, workout_id),
   FOREIGN KEY (workout_id) REFERENCES workouts (workout_id)
) ENGINE=INNODB;

CREATE TABLE sessions_swimming (
   started    datetime,
   workout_id int,
   meters     int,
   PRIMARY KEY (started, workout_id),
   FOREIGN KEY (workout_id) REFERENCES workouts (workout_id)
) ENGINE=INNODB;

This allows you to have complex workouts that do not follow the schema of previous workouts. You could have something like this very easily:

CREATE TABLE sessions_triathlon (
   started            datetime,
   workout_id         int,
   swimming_meters    int,
   cycling_meters     int,
   running_meters     int,
   duration_minutes   int,
   PRIMARY KEY (started, workout_id),
   FOREIGN KEY (workout_id) REFERENCES workouts (workout_id)
) ENGINE=INNODB;

Martin Fowler calls the above model "Concrete Table Inheritance" in his Patterns of Enterprise Application Architecture book. Bill Karwin also describes this model in his SQL Antipattens book, in the Entity-Attribute-Value chapter. He also describes the disadvantages in choosing an EAV model to tackle such a scenario.

On the other hand, if you want total schema flexibility, you could consider other NoSQL solutions instead of MySQL. These data stores do not not normally require fixed table schemas.

Answer 3

I'd say this calls for a 1:n relationship, where there is a master "workouts" table, and one unified "components" table that contains all the activities of a workout.

You'd have your main table workouts:

id   int
participant varchar(255)
date        datetime
...... any other workout related data

Then the child table workout_components:

workout_id  int          // Which workout this belongs to
tabindex    int          // Which sorting order this component has in the list
repeat      int          // Number of repetitions (e.g. 3 sets)
quantity    int          // e.g. 45 push-ups or 150 meters of cycling
quentity_unit varchar    // e.g. minutes or laps
activity    varchar      // push-ups, cycling .....

an example value would look like this:

workout table:

id          participant      date
1           Harry Miller     2010-08-21

workout_components table:

workout_id  tabindex     repeat      quantity     quantity_unit  activity
1           1            3           45           pcs            pushups
1           2            1           2            minutes        rope-jumping

Advantages:

• Not limited to specific activities

• Easy to query - every question related to how to get something from this kind of data structure has already been answered on SO

• Activities can be freely added to each workout