Temporal Databases

If you were following the replies to the Column-based Databases, Sybase IQ and SAND Technologies thread last week, here's another concept that'll get your head spinning : Temporal Databases.

I've been following this idea since a couple of weeks ago, when Doug Burns reported on a presentation Oracle's Rob Squire gave on the subject (slides here, but you need UKOUG membership to download). The concept behind temporal databases is that our existing way of storing relational data is much like a spreadsheet - rows of individual columns - that gives us a snapshot of the data in time. Temporal databases add a third dimension, time, that allows us to see the data as it was at a particular point in time. This blog posting by Adam Young gives a good example, using Google searches as an example, where a few years ago if you'd searched for "Troy" you'd have got links to a few historical web sites, now you'd just get stuff about Brad Pitt.

Of course the first thing you think of here is that Oracle already provides this, in the form of Flashback, but in the world of temporal databases this only makes Oracle a "rollback" database which is only part of the story - you can also get "historical" databases that store data with respect to valid time (as opposed to transaction time, which is what Flashback gives you the ability to access), and "bi-temporal" databases, which store data with respect to both valid and transaction time.

The slides by Rob Squire then go on to say why retro-fitting temporal features onto regular database application is difficult, because a simple join would then require lots of conditions, where clauses and so forth to make sure you just bring back the data for a particular valid time, and thing like constraints get quite interesting as a record could be in the temporal database as inserted at one point, then deleted, then inserted again at a later date - how do you deal with primary and foreign key constraints here?

Now the reason this caught my eye was it's obvious relevance to data warehousing; we store "temporal" data all the time, so that for example we can compare sales with today's regional structure compared to what it was a year ago. To do this we have to put in place "slowly-changing dimension" tables (this blog posting explains the background quite well) which apart from being a pain to set up and maintain, make the querying process a lot more involved and as Jeff Moss points out, could potentially make life hard for the cost-based optimiser. How nice it would be if the database handled the temporal aspect for us, automatically allowing us to look back in time to see that data as it was a year or a month ago, without having to code this bit in ourselves?

So, is anyone else looking at temporal databases, especially in a data warehousing environment? If so, is this as part of an Oracle solution, or are you using a new temporal-based database such as TimeDB? Feedback would be appreciated.

UPDATE: See also this article back from 1998 - it's obviously not a new concept.