Temporal Tables - Overview

The first feature I have looked at in DB2 is the concept of temporal tables, which as its name implies ( albeit with one of those words that makes something sound more complex that it needs to be ) is related to time.

So, what are temporal tables all about, simply the addition of a lifetime concept to data. Each piece of data ( at a row level ) is given a lifetime, i.e. a fixed start date and end date.

While time is clearly the key data behind this feature, I think the core concept is actually better described as tracking changes, and the offering of first order principle support in DB2 to do this.

In some respects you could argue this is like adding version control to your data.

Why do I want to add more time information to my data ?

Yup, sounds like a fair question, since if you poke around I am sure you will find there is already time information for your data, however that is always just the time the data was added / updated etc. What we are talking about here is lifetime of data, not just a single point in time.

Imagine that your boss agrees to give you a raise next month (+1 Boss), further assume that the payroll for this month has already been run ( so only a few days left in the month ). HR might reasonably assume they can just go and update your salary information now with the new value, since "clearly" the next time the value is going to be used is for the next payroll run.

Meanwhile, the auditors come in unannounced and validates the last payroll... needless to say ( but I will anyway ) things will not balance, best they can tell is that you were underpaid -- you of course take the moral high ground and explain to them that it is just a failing of the DB as it does not have temporal concept of lifetime added to your new salary, as that update should only take affect in next months payroll run, if they are trying to evaluate this months payroll run, they needs to use your previous salary information.

What is the solution ?

Clearly there are two immediate solutions to this category of problem

1. Teach HR to only update the data at exactly the right time. 
2. Don't make the salary update destructive, keep a history of all changes for given timeframes.

Option 1, is just littered with issues

1. Some poor HR person needs to be at work midnight on the last day of each the month 
2. Even if you do find a way to update at exactly the right time ( with a scheduled task so HR can continue to have a family life ), you have lost data. The previous salary information is lost.

Option 2, is clearly far superior, but comes with the potential challenges of having to adjust your entire application stack to take into account the concept of life time, so that queries into the DB know which version data to look at.

This is exactly where temporal tables come in, they offer you built in support creating this change history, with the added benefit of being able to do this with between zero to minimal changes to your existing product application stack above the DB.

What support does DB2 give me ?

There are 3 things that DB2 gives you ( I would argue only 2, but we can get into that later ), each of these I will touch on in future posts ( linked below as and when )

1. System Time Temporal Tables
2. Business Time Temporal Tables
3. Bitemporal Tables