How do you handle temporal data?

I have requirements that suggest we need to know not just what the value of an attribute is today, but what that value was at any point in time. For example, if you get married this June 10th, queries would show your last name as it was BEFORE you were married if they are run for any time before June 10th, and the queries would show your current last name for queries run any time ON OR AFTER June 10th. Of course, I can compare the dates in a BETWEEN clause, but how should the data be stored?

A coworker has proposed that each row have a BeginDate and an EndDate and if any attribute changes on that row, a new row be created with a new BeginDate and EndDate. They have also proposed the use of a "MagicDate" of 12/31/9999 for any records that are current. This seems woefully problematic to me, and goes against everything I hold true. For example, if you query how long ago was the end date for the records, you would end up with a result for some that says -7987 years ago--THAT clearly doesn't make any sense. I toyed with using a NULL for the end date for current records and using COALESCE in the BETWEEN clauses, but that requires developers to have an in-depth knowledge of the structure of the data to do queries (shouldn't they anyways?).

Another coworker suggested that we have a "CurrentPerson" as well as a "HistoricalPerson" table where CurrentPerson would only have a BeginDate and HistoricalPerson would have both a BeginDate and an EndDate. While I see the drawbacks of this solution, I find that in a way it better models reality because a CurrentPerson record doesn't have an EndDate, do they?

I'd LOVE to see any examples of how you guys handle this sort of thing, and I'd LOVE to hear your thoughts on this.

I've been reading Snodgrass, (http://www.cs.arizona.edu/people/rts/tdbbook.pdf) but it's slow reading, and I am not sure I agree with it.

Please advise,

v/r

Gooser

Quote:

Why do today
that which may not need to be done tomorrow --me

Gooser, We keep a "CurrentPerson" table and a "HistoryPerson" table. In the "HistoryPerson" table we have a start and end date, with the most current record having a null end date. Then use the COALESCE function like you were talking about in the where clause so that when a null end date is encountered is uses current date. Hope this helps.

born2program--

Thanks for the reply. Do you keep the current record in BOTH the CurrentPerson AND the HistoryPerson table? OR, do you do some sort of UNION of the two, (perhaps in a view,) to access both current and past records?

Thanks,
Gooser

_{http://www.cafepress.com/SQLPimp}

I've used a "current" plus "historical" table before. You could have multiple rows in the "current" table, but they had grouping key.
When something is added to the current table, a trigger would add it to the historical table, with today's date. We had to be able to compare current against X date, so we'd get the most recent record after X date, which might be the "current" record.

Stuff like this is always a pain, because the design is more dependent upon your reporting requirements, rather than your program usage requirements.

Lodlaiden

You've got questions and source code. We want both!
Oh? That? That's not an important password. - IT Security Admin (pw on whiteboard)

We record in both Current and Historical tables.

I have a series of tables for keeping previous name and address of any client, this is linked via a unique client reference number that is passed to the previous naem/address table at the time the entry is made, along with a date that this was added to the table.

You can in that way return all data linked through the clientid with a date, there by being able to say on 10th June 2011 Mrs Smith was called Miss Jones, however on 9th June 2012 she changed her name to Mrs Smith (as the date stamp on the prevoius name would be 9th June 2012).

Hope that helps, although agree that it really depends on your reporting requirements (never usally known until after implimentation, and usually after the requirement is needed),

Didn't anyone ever tell you? There's one thing you never put in a trap — if you're smart, if you value your continued existence, if you have any plans about seeing tomorrow — there's one thing you never — ever, put in a trap. … Me.