DB Efficiency , php application

Question

Hi there,

after getting my answer here: http://stackoverflow.com/questions/3537288/database-issue-how-to-store-changing-data-structure

i have another question.

lets say i have a workout:

first set: 60 push ups
second set : 55 push ups
third set: 50 push ups
firth set: 45 push ups
and so on..

I think it would be a waste to make an entry for each set, but making something like this:

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

seems primitive.

any suggestions?

Answer 1

It's not primitive. It's normalized. If you do it that way, you can easily tell how many sets of pushups you ever did, for example, or the average number of pushups per set.

One set per record is the most flexible way and you can get more meaningful data. Don't save as comma delimited. You are defeating the purpose of a relational database. Use couchdb if you want to store data like that.

Answer 2

You could always put quantity in its own table, then you end up with data that looks like this:

tblWorkouts:
----------------------------
workout id   activity   etc
----------------------------
1            pushups    ...


tblQuantities
------------------------------
qID     workout_id    quantity
------------------------------
1       1             60
2       1             55
3       1             50
4       1             45

The quantities are linked the proper record in the workouts table vai the workout_id - thats the relation in relational database :-)

This gives you some flexibility in doing things with the data once its in the database. This way its stored as number rather then a comma delimited string you have to parse and add up if you want to get any kind of meaningful data out of it.

Answer 3

One of the more important things in database design is minimizing redundancy. What you're describing is the way a lot of people I've worked with would solve the same problem.

One thing I would suggest would be to abstract the quantity_unit and activity to their own tables, since the activity would always be measured by the same quantity_unit, and having them separate means you only need to store the name of the activity once in some table.