[eluser]drewbee[/eluser]
Hey all,
I've got a situation that I have never ran across before. I have several ideas on ways to tackle this but would like some input as well.
Basically, I have a database of configurable exercises. This ranges anywhere from cardio, to weightlifting, swimming, whatever. Pick your poison.
Now, the situation here is that I want to use 1 table to create the configuration for all exercise types in the name of reporting. All exercises share at least a few attributes in common IE Weight lifting and running can both have a time attached to it. A lot of attributes are more specific to each exercise as well.
Now here's the even better part, each user has the ability to 'add' each exercise type to their account, only having access to the apparent 'turned on' configuration options for each exercise.
What is the best way of tackling this? Right now, I have two ways of tackling this and am looking for input on each way.
Method 1: use a exercise_attributes table;
For this, there will be a exercise table to hold the information, and each unique attribute will be a new row in the exercise attributes table. This makes things an easier setup, however can potentially make logging an absolute (giggidity).
This are, of course, mulled down just for examples
Referential Tables--
Exercise: id, name, description
Attributes: id, name, type, description
Exercise_attributes: id, exercise_id, attribute_id
and of course I would have to duplicate this for the user logging side. The type column of the attributes table naturally determines the type of data that can be entered whether its time, weight, reps etc etc
user_exercises: id, account_id, exercise_id
user_exercise_attributes: id, account_id, exercise_id, attribute_id, attribute_value
Naturally, this can become one hell of a database mess, and I could see it growing out of control, but leaves it open for incredibly easy configuration.
Method 2:
One table contains every possible attribute for all exercises. This also means there is a user_exercises table that contains every possible attribute.
Exercises: id, name, description, reps, weight, duration, speed, etc, etc
then of course our users table:
user_exercises: id, account_id, exercise_id, reps, weight, duration, speed, etc, etc
A lot cleaner, kinda denormalizes data a bit. Logging will be 20x easier though in terms of reporting and far less row inserts.
Thoughts on this one?
