This is the second article in a series. The first article is available here.
How to implement a temporal database
Not every database requires a temporal database implementation, but some do. We can help you get started. As discussed in our previous article, the SQL-2011 standard included clauses for the definition of temporal tables as part of the SQL/Foundation. However, this standard is very new and not yet widely adopted. For now, most of you will need to extend your current database tables to incorporate temporal concepts.
In this article we'll focus on temporal tables. These tables are the building blocks of temporal databases.
Temporal Tables – The Important Theories
Theory #1: Valid-Time State Tables
From Wikipedia: “Valid time is the time period during which a fact is true with respect to the real world.” A valid time state table lets you manage data whose values change over time. For example, the interest rate on a loan can be 5% for the first year, and 6% for the second year. During the second year you still want to know that the rate was 5% in the previous year.
Theory #2: Transaction-Time State Tables
From Wikipedia: “Transaction time is the time period during which a fact stored in the database is considered to be true.” When you successfully capture the sequence of states for a changing table, you have created a valid transaction-time state table. The tables by themselves now contain the required information to go back in time, or to “rewind” to a certain moment and see the data that was valid at that moment.
Theory #3: Bitemporal Tables
From Wikipedia: “Bitemporal data combines both Valid and Transaction Time.” Valid-time state tables and transaction-time state tables are orthogonal. You don't have to implement both at once; you can do one or the other, and even if you do both, you don't have to keep the information in a single table. For example, it is common to find designs where the transaction information is stored in different tables. In such cases, only the latest valid information is stored in one table while the other table contains the historical records.
If you decide to make a table both a valid-time and a transaction-time table, then you have created a bitemporal table. Using Snodgrass' words, “a bitemporal table allows a glorious expressiveness when exploring and extracting information” from such tables.
How to Implement a Temporal Table
In the following three sections, we briefly describe what you need to do with your tables to implement a temporal database.
We will refer to the Hudson Foods beef recall mentioned in our first article. This is an example provided in Richard Snodgrass’s book, illustrating a clear case of an organization suffering significant financial penalties as a result of not having a time-varying database. This example involves tracking cattle between different pens, and how that data changes over time. The information below is a super simplification of the example included in Chapter 2 of the book.
In a feed yard, cattle are grouped into “lots”. Cattle from one lot can be divided into multiple pens. We define a table LOT_LOC that tracks how many cattle from each lot reside in each pen of each feed yard. Cattle from each lot are moved from pen to pen, thus the data is varying over time.
LOT_LOC(LOT_ID_NUM, PEN_ID, HD_CNT, FROM_DATE, TO_DATE)
Let's focus on two columns: FROM_DATE and TO_DATE. These two columns render the table a “valid time state table”: it records information valid at some time in the modeled reality, and it records states; that is, facts that are true over a period of time. The FROM_DATE and TO_DATE columns delimit the “valid time” or “period of validity” of the information in the row. A key concept in this design is considering your “temporal granularity.” You should pick the right granularity for your problem. This could be days, hours, minutes, seconds, or whatever you need as a granularity level. For the previous table, the granularity level is a day.
Consider the following three rows:
(100, 3001, 32, 2014-06-01, 2014-06-02)
(100, 3001, 30, 2014-06-02, 9999-12-31)
(100, 3002, 02, 2014-06-02, 9999-12-31)
We can tell the following from the data: from June 1st, 2014, to June 2nd, 2014, there were 32 cows from lot 100 in pen 3001; on June 2nd, 2014, the cattle from lot 100 were split in two different pens. Thirty cows remained in pen 3001, and two cows were moved to pen 3002. This is the latest valid data. The first row is now invalid for the current date. As you can see, we can continue to add and modify this data. It will be possible to query the data such that we see an actual view of the data as it existed at a certain time. We could write queries that would allow us to trace back all the pens containing cows from a certain lot prior to or on a particular date.
The Design Works. But It’s Complicated.
This design would have solved Hudson Foods data issues, and would have resulted in a more focused and reduced food recall. This design concept can be used to implement temporal concepts into any standard database table. There are, however, challenges to this design, which we will discuss in the next article.
Read the next article in the series -> Temporal Databases: Why you should care and how to get started (Part 3 of 3)
Authors: Abel Morelos, Angela Holmes