Exercise database setup and logging

[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?

[eluser]TheFuzzy0ne[/eluser]
Hi,

I think I'd have to go with the first method. It just seems a lot cleaner to me, and it's how I'd expect it to be done (I don't know if anyone else would agree with that).

Sorry for sounding a bit dim, but I don't understand the problem you mentioned about logging. What are you planning on logging exactly?

[eluser]drewbee[/eluser]
The biggest issue that I run across, is that for each user, each exercise will log 1 row x number of exercise attributes.
Keep in mind, each exercise can be done multiple times a day/week/whatever.

I just see it growing exponentially out of control.

And even worse, the data mining for reports and what not are going to be a mess as well. Do-able, sure, but a mess none the less.

The data being logged is specific to each user so they can see their growth in terms of speed, endurance, weights, duration etc etc, specific to each exercise as well as 'parts' of the body and over all.

[eluser]TheFuzzy0ne[/eluser]
How about storing the attributes as a serialize array in the database, in the same row as the exercise?

[eluser]drewbee[/eluser]
I assume your talking about on a level within the exercises, as well as within the user data entry point?

That could potentially be even more messy for reporting, and could definitely only been done off of straight SQL with messy hacks. It would definitely be the cleanest in terms of configuration though.

Perhaps I just need to use the right amount of caching in just the right spots eh? IE Query to get users exercises, exercise attributes should be cached but the specific data retrieval will be the unique data.

Eh. This sucks. lol. I think I am going to go with method 1. In all reality, even though it has the potential to bloom completely out of control, every row will only have a few columns ie id, account_id, exercise_id, value.

Naturally, cutting down on the row inserts whenever possible is a plus as well IE if a user does not fill out a non-required field of an exercise, it will not be inserted.