Click here to Skip to main content
14,690,651 members
Articles » Development Lifecycle » Design and Architecture » General
Article
Posted 13 Feb 2007

Stats

70.6K views
21 bookmarked

Bitemporal Database Table Design - The Basics

Rate me:
Please Sign up or sign in to vote.
2.20/5 (10 votes)
13 Feb 2007CPOL
An overview of the concepts involved in bitemporal database design

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 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 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

  • 13th February, 2007: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

pfeds
Web Developer
United Kingdom United Kingdom
BBC B Man

Comments and Discussions

 
QuestionBi-Temporal data design and all the forgotten issues. Pin
Member 1225566919-Dec-17 19:20
MemberMember 1225566919-Dec-17 19:20 
GeneralFor more information... Pin
Marc Brooks26-Feb-07 21:04
MemberMarc Brooks26-Feb-07 21:04 
GeneralRe: For more information... Pin
Marc Greiner5-Mar-07 1:34
MemberMarc Greiner5-Mar-07 1:34 
GeneralBitemporal design Pin
ammonite5413-Feb-07 18:54
Memberammonite5413-Feb-07 18:54 
GeneralInteresting but lacking Pin
HellfireHD13-Feb-07 14:57
MemberHellfireHD13-Feb-07 14:57 
GeneralRe: Interesting but lacking Pin
Shawn Poulson14-Feb-07 3:48
MemberShawn Poulson14-Feb-07 3:48 
GeneralRe: Interesting but lacking Pin
pfeds14-Feb-07 5:27
Memberpfeds14-Feb-07 5:27 
GeneralRe: Interesting but lacking Pin
ncarey20-Feb-07 17:28
Memberncarey20-Feb-07 17:28 
AnswerRe: Interesting but lacking Pin
JohnFredC21-Feb-07 5:18
MemberJohnFredC21-Feb-07 5:18 
GeneralRe: Interesting but lacking Pin
rsocol15-Oct-10 1:56
Memberrsocol15-Oct-10 1:56 
GeneralSuggestions Pin
yesildal13-Feb-07 13:17
Memberyesildal13-Feb-07 13:17 
Good article, thanks for your time for putting it together. I wanted to share a few comments.

1) You could design the database with only one additional column and still represent the same information. Instead of keping From and To , you could only store "To" (or only "From"), since the price changes owerwrite each other, that should be enough. If I have two records for the egg sample you had such as

1 Eggs £1.20 13/06/2006
1 Eggs £1.25 31/12/9999

I can still determine what was the price of an egg before 6/2006, between 6/2006 and 12/9999, the only exception might be what if a product didn't exist in the warehouse for a period of time?, so you can't represent it with this model, that's true as long as that doesn't matter 1 column is enough. But on the other hand you have more validation problems with 2 column scenario such as what if 2 records overlap in a point in time? For example what if the records were as below...

1 Eggs £1.20 20/01/2006 13/06/2006
1 Eggs £1.25 13/03/2006 31/12/9999

So this scenario has its own problems too...

2) If I ever need to extract the time-based information from the DB (what was the value of an item in this time frame?) that was the first thing i'd do, add 1(or 2?) columns to the DB to represent the timeframes. I just don't see where is the value of Bitemporal databases for it to be an article? This is the most straightforward solution I could think of (and probably what everyone else also would think of as well and that everyone would implement anyways), and don't think it deserves a special title or naming since it's simply adding 1-2 columns to the DB as the need arises.

Regards,

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.