Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2007

    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.

    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 Attached Thumbnails dbDesign.jpg  

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    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 | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Aug 2007
    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.

  4. #4
    Join Date
    May 2005
    San Antonio, Texas
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts