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.
- Temporal Tables (Modelling Valid Time)
- Example 1 - A Temporal Table
- Bitemporal Tables (Modelling Valid Time & Transaction Time)
- 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 3rd 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
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
£1.20 until 13th June 2006, and they are currently
- We can see that
£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:
12 crates of
milk in stock between 12th and 18th November. At that point,
7 crates were removed and were (likely) delivered to warehouse
- 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
LOND01. On this day,
5 crates were removed.
- At the current time, there are
5 crates of
10 crates of
7 crates of
- 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.
- 13th February, 2007: Initial post