Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601

    Unanswered: Designing a date-dependent table

    If you have a table that has records which are applicable over a date range, is there a preferred design for dealing with the dates?

    A simple example might be an employee table, which might have multiple employees, with each employee have multiple records, each record being applicable over a particular date range.

    For instance:
    Code:
    EmpID EmployeeName    StartDate  EndDate    AdditionalFieldsOfData
    00001 Jones, Jerry    4/6/2011   8/10/2011
    00001 Jones, Jerry    8/11/2011  1/7/2012
    00001 Jones, Jerry    1/8/2011   12/31/3000
    00002 Fredricks, Fred 8/3/2011   10/15/2011
    00002 Fredricks, Fred 10/16/2011 12/31/3000
    One could model this table with an implied EndDate (of 12/31/3000), as in:
    Code:
    EmpID EmployeeName    StartDate  AdditionalFieldsOfData
    00001 Jones, Jerry    4/6/2011
    00001 Jones, Jerry    8/11/2011
    00001 Jones, Jerry    1/8/2011
    00002 Fredricks, Fred 8/3/2011
    00002 Fredricks, Fred 10/16/2011
    Or, one could imply the beginning date and store the first record's beginning date elsewhere, in a date hired table, or in additional field on each record. As in:
    Code:
    EmpID EmployeeName    EndDate    HireDate AdditionalFieldsOfData
    00001 Jones, Jerry    8/10/2011  4/6/2011
    00001 Jones, Jerry    1/7/2012   4/6/2011
    00001 Jones, Jerry    12/31/3000 4/6/2011
    00002 Fredricks, Fred 10/15/2011 8/3/2011
    00002 Fredricks, Fred 12/31/3000 8/3/2011
    Can anyone refer me to a discussion of this topic? Has anyone dealt with this design issue before and have an opinion on which is the best method to use?

    [On re-reading my post, it seems to me that the second example would have to end up looking like the last example, as you would need a place to store an EndDate (termination date) for records where the end date was other than the implied EndDate of 12/31/3000.]
    Last edited by PracticalProgram; 01-15-12 at 18:38.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I use your first solution. Both the start and the end dates are NOT NULL.
    But the end dates get 12/31/9999 as default value.

    By declaring the EndDate as NOT NULL and giving it its maximum value, instead of leaving it NULL while undefined, it becomes a lot easier / more efficient to write queries:

    Code:
    DECLARE @CurrentDate DATE
    SELECT @CurrentDate = DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)
    
    SELECT ...
    FROM ...
    WHERE @CurrentDate BETWEEN StartDate AND EndDate
    Compared to
    Code:
    SELECT ...
    FROM ...
    WHERE @CurrentDate BETWEEN StartDate AND COALESCE(EndDate, '99991231')
    I'm not sure if this answers your question though.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There are a huge number of variations on the theme and if an existing database has a time period management methodology then stay with it! In other words, if you have a system with a StartDate and EndDate in every table with NULL values for unknowns, then continue to use it even though it leads to inefficient queries.

    The method I prefer is to have a StartDate and an EndDate that are NOT NULL and that have default values. I prefer to use UTC times because when a system grows and becomes distributed this is easy to handle and easy to report no matter where or when the data is entered. It also means that data can be entered/reported world-wide without complicated massaging of the data.

    Unless there is some compelling reason to use another value, I suggest using 1753-01-01 00:00 as the default start value and 9999-12-31-23:59 as the default end value. Note that this leaves you one extra minute worth of end values past your default, which can be really handy for manually massaging data.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    don't you mean less that one second, not one minute?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by r937 View Post
    don't you mean less that one second, not one minute?
    I meant one minute... Call me an old fogey, but I like to leave myself a bit of room to play in and a second wouldn't be enough to make me comfortable.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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