Sunday, April 15, 2012

On temporal extension to SQL:2011

I have written before about the recent return to the bi-temporal databases, in conjunction with DB2.  In the 1990-ies was the first attempt to create bi-temporal extensions to SQL, at that time there was a language war, some of it is reflected in the book that I have co-edited, published in 1998.  Now after some attempts, SQL:2011 does include support in bi-temporal databases.  The terminology was changed from the original terms.  What was called in the original version - "valid time" is called in the SQL version "application time',  an what was called in the original version - "transaction time" is called in the SQL version "system time".   
More details about the SQL extension can be found in the overview presentation that Craig Baumunk uploaded to slideshare.      As I have written before, temporal database is vital for maintaining historical events, and thus the importance of this standard, and the supporting databases to event processing application is noticable


woolfel said...

Traditional row based design of relational database is not well suited to temporal databases. Given a temporal database appends for insert and update, a different design is needed. I've studied the old TSQL2 specification and tried to read as much as I can on the topic, but I feel it isn't sufficient to meet real world business needs. Take property and casual insurance for example. Not only is there valid and transaction time, there's also branching. Often changes are back-dated and future-dated. Standard bi-temporal techniques do not and cannot address these issues. I've built several bi-temporal databases over the years for different kinds of applications using RDB and they just don't scale for anything remotely complex. In casualty insurance industry, an entire object graph has to be stored with three dimensions: valid time, transaction time and branch.
I hope the new effort looks beyond bi-temporal and really tries to solve existing business problems. Within the car insurance industry, this is a huge problem.

doucetrr said...

Woolfel - would like to understand the branching/3rd dimension. Are there any resources you can share that explain this further?

woolfel said...

There are no academic papers or written resources on the 3rd dimension. I've spend the last 3 years researching and building a temporal database that supports 3-dimensions. In casualty insurance the main reason for the third dimension is back-dated changes due to human error. When that happens, there's multiple records for the same valid time period. This is a huge problem for all the car insurance companies in the USA. if you want to learn more, you can email me directly at woolfel AT gmail DOT com.