Designing a date-dependent table

PracticalProgram · #1 (**permalink**) 01-15-12, 17:31

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.]

Wim · #2 (**permalink**) 01-15-12, 20:10

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.

Pat Phelan · #3 (**permalink**) 01-16-12, 13:14

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

r937 · #4 (**permalink**) 01-16-12, 13:27

don't you mean less that one second, not one minute?

Pat Phelan · #5 (**permalink**) 01-16-12, 16:49

Quote:

Originally Posted by r937

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

Internet Services

Web Development

Digital Marketing

Consumer Tech