Opinions About the Design of my Database

Question

Opinions About the Design of my Database

Polymorphist

SSCommitted

Points: 1909
More actions
December 22, 2010 at 11:22 pm

#232283

Hello,
I have been storing data regarding my cardio exercise in an Excel spreadsheet for the past three months. Each particular tab in the worksheet represents a new week. Every day that I exercise I open the current tab and enter the date, the day of week, the particular form of cardio (elliptical, treadmill, bike or running), the number of miles I ran, rode, etc, the calories I burned and the number of minutes the session lasted. Once those values have been entered, I have formulas in Excel calculate my minutes/mile and the number of grams of fat I burned during the workout session.
I like this spreadsheet, but I have been wanting to put this into a database for a while now and then put a web application on top of it so that I can access the sucker from anywhere. I may also extend it and include other info as necessary. I came up with a relatively simple design for a SQL database and I think it's a good start. However, I do not have much experience with database design and can always use opinions.
I have attached a screenshot of what I have thus far for design. One note, the FitnessDetails table has a PK of UserID in case I want to extend the DB and application to other data which I might want to track (though I don't know what that'd be right now).
Can someone take a look and give me some opinions on my work?
Thanks!!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

TomThomson SSC Guru Points: 104773 More actions · Answer 1

I can't see any reason it wouldn't be OK. But a couple of things look slightly eccentric (not wrong, just strange choices).

Not sure what is the reason for splitting the workout data into three relations, as they share a primary key and if merged they would still (as far as I can see) be in 5NF.

Also not sure why there are three excercise type attributes in workoutmethod, rather than just one attribute that can take three values.

Tom

Polymorphist SSCommitted Points: 1909 More actions · Answer 2

Thanks for the response, Tom. One reason why I split the WorkoutMethod into their own table is so I can use those values in a drop-down menu in the application. I'd rather have them available as choices rather than enter them free-form into a textbox lest things get too messy in record-keeping. I figured that this was the best way to do it.

Do you agree with this, or is there a better way to do it?

I might also have friends keep track of their performance through my application as well, so keeping it as tidy as possible is probably the best way to go. I did move the fields from the WorkoutMetrics table into the WorkoutSession table, though. Again, any final recommendations which you can make would be appreciated. I work with data all day long but rarely, if ever, do I ever get to design a database so this is very helpful.

Thanks!!

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 3

I agree with Tom... It would appear that there is and always will be a 1:1 relationship between the fitness details, the session metrics, and the date of the session and none of the columns in any of those tables looks like it'll ever be null. There will be those that disagree but I believe all that information is just about the session and should probably live in a single table. I agree that the workout method should be in a separate table but the key for the workout method should be stored in the session table as an FK to the workout method instead of how you currently have it.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TomThomson SSC Guru Points: 104773 More actions · Answer 4

Yes, I agree that it's useful having a dropdown list is much better than having free text input, and you certainly want to populate the dropdown from the database. But as Jeff says, that is better handled as a new entity with its own primary key (referenced from the session table as a foreign key), and you construct the dropdown by selecting all from that table. This makes it trivial to add new workout methods if you ever need to (no code changes in the app or in the database, just add another row to the table) - and in my experience it always turns out necessary to add things over the years, no matter how sure you are at the start that no addition will ever be needed to an enumeration - as well as taking up less space in the database than your current arrangement.

Tom

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 5

Just a first thought.

Why is userid the pk of your fitnessdetails entity ?

Can a user only have a single fitness detail ?

IMO a user can change bodiweight and bmr over time, isn't it ?

I would also add length to the data (because that may also change in time)

btw isn't bmr a calculated value ? (maybe make it just a calculated column)

I would join workoutsession and workoutmetrics because IMO these are 1:1 related and move the date column to the fitnessdetails entity.

If you intend to store typical training sessions (cfr templates) out of which users can chose, I see a point for workoutsession, otherwise I dont.

IMO workoutmethod should be a parameter entity, defining the individual types of workout ways. For the moment containing 3 rows.

How about adding an active indicator or datestart dateend kind of info.

Unless you plan these parameters always to be available and active.

You would need an extra users entity, providing name, ... info.

Are you only going to register full workouts ?

If not, you may want to pull over some extra columns regarding distance, time, .. to your workoutsession entity, to be able to handle these exceptions. In that case, you'll have to pose the extra question if you are only going to store the exceptions in these columns, or always add the template values in these rows.

Keep in mind to add indexes for your fk columns, if you translate your entities to tables.

Happy holidays.

Johan

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Polymorphist SSCommitted Points: 1909 More actions · Answer 6

Excellent, thanks for the input, everyone. I have modified my design, please find it attached.

Some notes -

ALZDBA - you are correct, I did not plan for the fact that body weight and BMR can change over time (as can age, etc). So for this reason, I have created a FitnessUser table which lists each user once, then a FitnessDetails table which contains all of the (hopefully) changing body metrics. This does pose one question.

The biggest challenge I seem to have is how often users should weigh/calculate personal metrics on themselves - a large University of Minnesota fitness study says daily, while psychologists say it's best to do it weekly. For this reason I decided that it's better to have a FitnessDetails table with a DATE field to capture the last time a user checked their personal body weight and metrics as opposed to making it a part of every session. Additionally, I know that at my gym scales are not always available - they always seem to be broken or out of commission. For this reason, for me personally it's easier to do it weekly but maybe for someone else they prefer daily. I don't know if you guys agree that letting the user decide when to calculate their body weight and metrics and recording it in a FitnessDetails table is a good idea, or if I should impose a weekly standard in the application. I'm feeling as if i'm going on a tangent here, so I guess the real question i'm asking is this: is it okay to be recording changing user body metrics in a FitnessDetails table, or should I leave that to the WorkoutSession table?

I generally feel that this current design is okay and will work, but other opinions are always great.

Thanks!!

Johan Bijnens SSC Guru Points: 135255 More actions · Answer 7

Be sure to start off with all columns you need in your data system.

e.g. maybe it's better to add birth date to your users table and calculate age yourself (inline or persisted) than to let the user provide that data himself and commit typing errors.

Here are some interesting articles that may help you with your quest:

Normalization

http://www.sqlservercentral.com/blogs/abhijit_desai/archive/2010/09/07/noramlization.aspx

Codds rules

http://www.sqlservercentral.com/articles/Advanced/coddsrules/1208/