tags:

views:

53

answers:

3

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..

+1  A: 

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 
Chinjoo
the problem though, is that its a lot of data, lets say I have 100 workouts, its a lot of data scattered for each practice .
WEBProject
I need a way to store the data itself (sets, its value etc) in one table
WEBProject
+1  A: 

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.

Daniel Vassallo
good one.. but if tomorrow new workout is introduced, you need to create a new table, ie. for every workout, you need to create a new table.
Chinjoo
the thing is, each member could create its own "session". thats why I need a creative way to store the data.
WEBProject
@Chinjoo: Yes, SQL works best in these situations. An EAV model like the one you suggested can be a solution, but that comes with certain disadvantages: Mainly it is difficult (impossible) to enforce data integrity, and certain queries become very difficult to build. (The database won't stop you from inserting 300 meters of pushups, even though it doesn't make sense).
Daniel Vassallo
the thing is i want to be able to create my own workouts easly, without opening a new table each time.
WEBProject
@WEBProject: How many workout types do you image to support? Will your application be able to handle complex workouts like the "triathlon" example I described?
Daniel Vassallo
+1  A: 

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

Pekka
seems OK, but lets say i have each workout 5 components , and a 100 workouts, wouldn't it be inefficient ?
WEBProject
@WEBProject inefficient in what way? You mean for the script or for the database? Don't worry. Modern hardware is built for processing trillions of rows in the blink of an eye. The amounts of data you are likely to manage with this are far, far away from justifying thoughts about de-normalization to optimize performance.
Pekka
got you, thanks a lot, you were very helpful!
WEBProject