Bitemporal Database Table Design - The Basics

Introduction

I am a developer more than a DBA so this is just a basic overview. I have been looking into bitemporal tables and found it hard to find any information on the subject that was in simple terms. So here is a basic example of what it's all about...

Bitemporal database design is a method of storing time dependant data records to represent both the history of the facts and the history of changes to the records in the database. Bitemporal databases permit queries over two orthogonal time dimensions: valid time and transaction time. Valid time is the time when a fact is effective in reality. Transaction time denotes the time when the record is effective in the database.

Contents

1. Temporal Tables (Modelling Valid Time)
2. Example 1 - A Temporal Table
3. Bitemporal Tables (Modelling Valid Time & Transaction Time)
4. Example 2 - A Bitemporal Table

Temporal Tables (Modelling Valid Time)

Most database queries are only concerned with data as it is now, such as "What is the price of an item?" It is tricky to cater for queries such as "What was the price of an item on November 3^rd of last year?" or "How long was an item £2.99 for?". By adding two columns for valid start time and valid end time, it is possible to represent these queries.

An easy way to implement this is in two phases. Phase one will be to design the database with normal methods, such as normalization. Phase two is to create the temporal additions to necessary tables.

Example 1 - A Temporal Table

Here is a conventional table for Product:

ID Name  Price
1  Eggs  £1.20
2  Milk  £0.45
3  Bread £0.30

What he can retrieve from this table is information such as "How much is Milk?"

By adding two date columns then, we can perform temporal queries:

ID Name  Price From       To
1  Eggs  £1.20 20/01/2006 13/06/2006
1  Eggs  £1.25 13/06/2006 31/12/9999
2  Milk  £0.45 20/01/2006 01/01/2007
3  Bread £0.30 20/01/2006 31/12/9999

From this table, we can extract far more information:

• We can see that Eggs were £1.20 until 13th June 2006, and they are currently £1.25.
• We can see that Milk was £0.45 until 1st Jan 2007. There is currently no price (i.e. Milk is no longer sold).
• We can see the entire price list at any point in time.

So from this table, we can not only deduce the current prices of products, but we can also determine the price history of each product.

Bitemporal Tables (Modelling Valid Time & Transaction Time)

As seen above, the temporal table allows for periods of time to be stored in a database. This is ideal for showing state changes of objects (valid times). Bitemporal design expands on this to model not only valid time, but to additionally model transaction time. Transaction time represents the physical time at which a transaction happened within the database.

Take for example a shipment arriving at a warehouse. This could happen on Wednesday (which would be the valid time that the shipment was in the warehouse). An operator might update the system on the Thursday to state when the shipment arrived. The moment they add the record to the system would be the transaction time. What this allows us to determine at a later date is when that shipment was believed to be in the warehouse, and also when that shipment was known to be in the warehouse according to the database.

Example 2 - A Bitemporal Table

Here is an example of a Stock table that details the stock in various warehouses:

ID  Stock Qty WHouseId ValidFrom  ValidTo    TrxStart   TrxEnd     OperatorId
101 Milk  12  LOND01   12/11/2006 18/11/2006 13/11/2006 18/11/2006 1111
115 Eggs  15  LOND01   12/11/2006 23/11/2006 13/11/2006 24/11/2006 1111
101 Milk  5   LOND01   18/11/2006 31/12/9999 25/11/2006 31/12/9999 1201
101 Milk  7   LOND02   18/11/2006 31/12/9999 25/11/2006 31/12/9999 1201
115 Eggs  10  LOND01   23/11/2006 31/12/2006 24/11/2006 31/12/9999 1111

From this table, we can determine information such as:

• Warehouse LOND01 had 12 crates of milk in stock between 12th and 18th November. At that point, 7 crates were removed and were (likely) delivered to warehouse LOND02.
• The milk was believed to be moved on 18th November but was only recorded on the 25th.
• Up to 23rd November, there were 15 crates of eggs in LOND01. On this day, 5 crates were removed.
• At the current time, there are 5 crates of milk and 10 crates of Eggs in LOND01, and 7 crates of Milk in LOND02.
• We can also deduce the average time delay between when an event occurs and when it is logged in the system.
• The additional OperatorId can tell us who recorded each transaction.

History

• 13^th February, 2007: Initial post