Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Opinions About the Design of my Database


Opinions About the Design of my Database

Author
Message
Polymorphist
Polymorphist
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 265
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!!
Attachments
FitnessTracker..jpg (70 views, 62.00 KB)
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10703 Visits: 11998
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
Polymorphist
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 265
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
Jeff Moden
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45006 Visits: 39880
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10703 Visits: 11998
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

ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6972 Visits: 8839
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

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


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


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
Polymorphist
Polymorphist
Valued Member
Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)Valued Member (57 reputation)

Group: General Forum Members
Points: 57 Visits: 265
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!!
Attachments
FitnessTrackerII.jpg (35 views, 57.00 KB)
ALZDBA
ALZDBA
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 6972 Visits: 8839
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/

Johan


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere w00t

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


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


"press F1 for solution", "press shift+F1 for urgent solution" :-D


Need a bit of Powershell? How about this

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


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search