Results 1 to 5 of 5

Thread: Design decision

  1. #1
    Join Date
    Jul 2007
    Posts
    96

    Unanswered: Tables design decision regarding datetime values and calendar table

    Hello everyone

    I'm designing a new database for a project. In this database I have a calendar table with with the following columns:

    id, dateValue, year, quarter, week, month, englishMonthName, day, englishDayName, dayOfTheYear, isWeekendDay
    This database also has a session monitor that logs every access to the database, with a relation to the calendar row id. This way, I can make database access reports, without replicating the date value.

    My question is: for a membership table should I follow the same principle and relate member row to the session monitor, which in turn, relates to the calendar or should I put the date just there?

    Some of the tables of this database will have to handle some heavy load, both for updating and selecting. This said, my question is should I make a link or put the date just there to extinguish the need to make 2 joins just to know when something was registered / updated? If I only place the relation to know the date I'll have to do something like:

    SELECT
    DATEADD(ss, x.timeOffsetInSeconds, c.dateValue) AS date
    FROM
    <somewhere> x
    JOIN sessionMonitor sm ON sm.id = x.sessionMonitorId
    JOIN calendar c ON c.id = sm.calendarId

    Instead of just doing a select x.lastUpdateDate

    How would you gurus usually deal with these situations?

    Best regards
    Last edited by Diabolic; 12-10-07 at 11:34.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    One thing I'm not certain about - why is there an id column? Why not just use the date field as a natural unique key?
    I reckon even Pat wouldn't expect dates to change.... (private joke Diabolic )

  3. #3
    Join Date
    Jul 2007
    Posts
    96
    The id column is there only becaused I assumed that - from a performance point of view - it would be better to create a relation between two ids and not a date value, for the more unique it can be.

    If I stick to the idea of relating every date in every table to the calendar table it would make sense (in my mind) to be relating IDs and not values.

    Still, unless the underlying answer in your reply is that if I link values I won't have to do the joins, which is true, I still have the question of how to approach this? link everything to the calendar table or just put the date value in every row that is inserted / updated.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yes, numbers can occasionally be more unique than dates... NOT!!

    even if they were, that's still not a good enough reason for a surrogate key in this case



    if you "link" a row in a data table to a date in a calendar table using the actual date, you get the following benefits:

    1. your data table will never contain any date that is not in your calendar table

    2. for any date in the data table, you can actually tell what date it is without looking it up in the calendar table

    you can achieve both of these using the date as the key, but only the first of these using a surrogate number
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jul 2007
    Posts
    96
    Thank you both for the enlightment.

Posting Permissions

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