If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > General > Database Concepts & Design > Comments on this partial design?

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 10-04-07, 14:25
TravisJ TravisJ is offline
Registered User
 
Join Date: Aug 2007
Posts: 10
Comments on this partial design?

I've been thinking about how best to design for a particular situation, and I wonder if any of you have comments about what I've come up with. I've attached a JPEG of the design if that's easier to understand.

Situation:
Database to store information on one's running.

A person can run multiple times per day, and can run in different ways (sprint, jog, powerwalk, etc.):
-Table: runningSession with a session_id.
-runningSession table contains a FK date_id to the dates table
-runningSession table also contains a FK type_id

Each runningSession can have information about multiple laps stored
-Table: laps with a lap_id, time, and distance
-Table: laps_sessions that links to runningSession via session_id and laps via lap_id. Also contains a lapNumber to say which lap it was.

Is this properly normalized (see the attached JPEG if my description of my tables wasn't clear) and a good design? I think so, but I'm still learning database design.
Attached Thumbnails
Comments on this partial design?-dbdesign.jpg  
Reply With Quote
  #2 (permalink)  
Old 10-04-07, 15:06
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,524
remove the dates table -- there is no need to use a surrogate key for dates, the natural key is sufficient and more desirable, just put the date itself into whatever table needs a date column

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #3 (permalink)  
Old 10-05-07, 12:24
TravisJ TravisJ is offline
Registered User
 
Join Date: Aug 2007
Posts: 10
That makes sense, thanks.

What about getting rid of the laps_sessions table, and moving session_id and positionNum into the laps table?

While there can be multiple laps per session, there is no reason that a single lap can have multiple sessions.
Reply With Quote
  #4 (permalink)  
Old 10-05-07, 17:12
amthomas amthomas is offline
Registered User
 
Join Date: May 2005
Location: San Antonio, Texas
Posts: 134
If lapNumber is just a recording of how many laps were run in a session, then it is just a piece of data for runningSession.

If you want to record data about each lap run, then I would fold laps_sessions into laps and just have a foreign key linking laps to a runningSession.

I think distance is the weird piece of data here. Either a session has laps that are all the same distance or a session has laps where each laps can vary in distance. If each session has a single distance then that data would go in session, but if the laps could vary then it would go into the lap data. The latter method is the most flexible (can be used in either case), but requires more input since you have to put lap distance in each time. A UI could be 'smart' about this though and just remember what you entered for the first lap.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On