In what cases are bitemporal tables actually used?

Question

I am trying to collect information about temporal databases. I know it is not a modern technology, but I saw that many people who work with databases don't ever know how temporal approach works (I asked some senior programmers and system analysts about temporal databases and they answered something like "Huh?").

I know there are valid-time state tables and transaction-time state tables, along with bitemporal tables. I think that bitemporal tables are way too complex for most usages, because nowadays space is not a problem anymore, and it is more efficient to write the same information on 2 different tables, even if data is redundant. However, I made many searches online trying to see where bitemporal tables are actually used, but I didn't find anything useful.

Are there cases when use of a bitemporal table is really convenient than valid-time and transaction-time state tables separately? Are there real-world examples?

Answer 1

Of course! Take for example, balance sheet data. You will find that this information will change from WD1 (Working Day) to WD x due to late arriving data, adjustments, manual errors and suchlike.

In order to enable repeatable reporting, audit trail and temporal comparisons, a record must be kept of 'old' (invalid?) results. Bitemporal is a great way to manage such updates, especially on an intraday basis. I don't think it's that complicated from a user perspective - just another filter on the where clause.

I admit that the loading process is complicated, but it's not that bad.. I literally just finished writing a generic transform (in SAS, coping with all scenarios for a unique business key) and it took a single day.

Coming back to use cases.. Having both valid (business) time and transaction (version) time on the same table enables:

• Repeatable results (having separate tables and corresponding updates could mean getting different results for the same query on two different days)
• Comparable results (can answer questions such as "what was the value of X, as we knew it at time Y?")
• Rapid results (only dealing with a single table, updated in a transparent and easy-to-query way).

In this sense it is an appropriate structure to use on many, if not all tables in a DWh.

UPDATE 2020: A bitemporal data transform for SAS (both SAS 9 and Viya) is available with Data Controller for SAS. A demo version is available: https://docs.datacontroller.io/dcc-tables/#var_busfrom-var_busto

Answer 2

I think your question raises more issues but it all comes down to how much is enough. I developed a Bi_Temporal SQL Server engine that supports object versioning and relationship by time as well as all the other beautiful parts of Temporal DB's.

This was because the project needed to be able to be rewound to a place in time and see everything as it was at that time. I mean everything including data, relationships and User access. It was the most complex thing I have built but in the end it was so complex no-one else could maintain it, or understand what was happening. So there was a real world use case and a deliverable.

Is not everyones cup of tea as you have to be able to think in time dimension as well as object version changes as all db's do.

Hope this helps someone. I know the post is old but as it was the first I found when searching Temporal DB's it might be of interest to someone.