Many data-centric systems require the ability to back-date and/or future-date changes. That is to say they need to record the “real world” history of data items. They also usually demand the capability to report “what was known, when it was known” – Thus combining the “real world” history with a log or journal of changes as and when they were applied to the database.  

If implemented well, such capabilities allow for forensic interrogation of the state of a database at any past, present and future date in real world time, and at all stages of the writing of the records. 

This can be vital to a business – especially where data in a system is directly attributed to safety or financial matters.  If we update the system to reflect what we are planning to do, we must still be able to see what the state of our data is “Now”, and if there were to be a problem – an accident perhaps – it will be very important to determine the state of the data at the time various decisions were made, i.e. to navigate to the states of data before and after corrections were applied.

It comes as quite a surprise to most systems architects then, to realise that this is still a very difficult domain.

The obvious answer that occurs to most people is to apply varying combinations of DATE or TIMESTAMP columns to tables; EFFECTIVE_START, EFFECTIVE_END, DATE_MODIFIED and so on.  This is not unreasonable; indeed it is the only mechanism available.  However, the manner in which this is done has a huge impact on both project functionality and costs.  These costs are often overlooked in the estimating process, because the complexity introduced is not appreciated until the hapless developer starts trying to write the SELECT and INSERT, UPDATE, DELETE statements. 

The tendency in the early stages of a project is, if not to be entirely dismissive of the issue, then to stick the timestamp columns on a data model and consign it to the “we’ll think about it later” list, based on the assumption “how difficult can it be?”

For generic academic discussions of the subject, two good places to start are:

• Temporal Data and the Relational Model, by Chris Date, Hugh Darwen and Nikos Lorentzos

Note that the rubric on the referenced site says, quote: “Current DBMSs provide essentially no temporal features at all” and further “temporal databases are virtually certain to become important sooner rather than later, in the commercial world as well as in academia”.  This book was published in 2002, and the astonishing fact is that these two assertions are still pretty much true in 2012.  However, some moves are being made – Oracle’s flashback query and flashback data archive, IBM’s temporal tables, are nods in the general direction, but as yet still do not cut the mustard.  Why?  Well because it really CAN be THAT difficult.  These technologies only address the “log of times that changes were applied to the database” dimension – the dimension most commonly referred to as “Transaction Time”.  They do not manage to combine this with “Valid Time” – the “real world” history of data items. 

• Developing Time-Oriented Database Applications in SQL by Richard T Snodgrass

All of the authors and contributors to these two books are high ranking academics who, without doubt, speak authoritatively on the subject.  A quick skim of either publication will hopefully firm up the impression that – Yes, it’s difficult.  A more thorough reading will do the job for certain.

Assuming the reader to be a busy IT Architect, without the luxury of time to delve into the minutiae of bi-temporal data (so called because it models the combination of “System Logged Time” and “Real World Time”) and the difficulties it presents, a brief explanation of the problem follows.

A very simple schema is the start point.  Note the absence of DATE or TIMESTAMP columns.

To manage transaction and valid time in the application requires the addition of timestamps, so here is a common first step on the first table:

The addition of the timestamps gives rise to the first challenge: How to refactor the primary key? Leaving it as it is (on CODE) clearly defeats the object of adding the timestamps in the first place as only one record could exist with a given CODE.  Adding various combinations of the new timestamps into the key will also be found to be much less helpful than it would first appear:

This is starting to look possible. Looks can be deceiving.  As transaction times should always be written as “System Time”, then adding the TT_BEGIN into the primary key will be sufficient for transaction time.  This is because the TT_END is really a denormalisation.  TT_END is in fact the TT_BEGIN of the next “transaction” or version of the row. 

The complexity of what happens when daylight saving is catered for overlooked for now, but of course the clocks going back and running the same hour again will cause the system to attempt to introduce transaction time duplicates.

However there is still a problem with valid time.

Efficient software is layered.  That is to say functions are specified, written and tested once, then reused as required.  The resulting code is efficient because it significantly reduces the trio of specification, coding and testing requirements.  Performance efficiency is orthogonal to this, and while crucial to the success of a project, it has not been in the scope of this discussion.

It is interesting to note that most database-centric projects do not attempt to subsume (reuse) any given established mechanism for handling time, preferring instead to re-invent the wheel.  There is, of course, good reason for this.  There are no well-known, ergonomic “wheels” available.  However, they are there and can be used.  For a good critique of the cost/benefits of using one such, please refer to Gartner ID Number: G00159368 (The Temporal Database: An Idea About Time Whose Time Has Come, by Frank W. Schlier, 2008).  In this document Schlier recommends that “Database vendors should include TDM [Temporal Data Management] in their core databases or provide a temporal database layer to move TDM from the application domain to infrastructure.”

He also describes how Oracle Consulting in the U.K. has shown Gartner a promising transparent layer for the Oracle RDBMS that provides what appears to be seamless embedding of TDM into the database and the SQL that support it. This approach integrates TDM into standard SQL in an extremely straightforward and simple way. The layer provides the application developer an extended SQL that includes the commands necessary to manage temporal data within SQL.

The layer that was shown to Gartner is an autonomous Time Travel capability available through this website.