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)

Coming Soon!

Authors: Abel Morelos, Angela Holmes

© 2019 Data Science Central ® Powered by

Badges | Report an Issue | Privacy Policy | Terms of Service

**Most Popular Content on DSC**

To not miss this type of content in the future, subscribe to our newsletter.

**Technical**

- Free Books and Resources for DSC Members
- Learn Machine Learning Coding Basics in a weekend
- New Machine Learning Cheat Sheet | Old one
- Advanced Machine Learning with Basic Excel
- 12 Algorithms Every Data Scientist Should Know
- Hitchhiker's Guide to Data Science, Machine Learning, R, Python
- Visualizations: Comparing Tableau, SPSS, R, Excel, Matlab, JS, Pyth...
- How to Automatically Determine the Number of Clusters in your Data
- New Perspectives on Statistical Distributions and Deep Learning
- Fascinating New Results in the Theory of Randomness
- Long-range Correlations in Time Series: Modeling, Testing, Case Study
- Fast Combinatorial Feature Selection with New Definition of Predict...
- 10 types of regressions. Which one to use?
- 40 Techniques Used by Data Scientists
- 15 Deep Learning Tutorials
- R: a survival guide to data science with R

**Non Technical**

- Advanced Analytic Platforms - Incumbents Fall - Challengers Rise
- Difference between ML, Data Science, AI, Deep Learning, and Statistics
- How to Become a Data Scientist - On your own
- 16 analytic disciplines compared to data science
- Six categories of Data Scientists
- 21 data science systems used by Amazon to operate its business
- 24 Uses of Statistical Modeling
- 33 unusual problems that can be solved with data science
- 22 Differences Between Junior and Senior Data Scientists
- Why You Should be a Data Science Generalist - and How to Become One
- Becoming a Billionaire Data Scientist vs Struggling to Get a $100k Job
- Why do people with no experience want to become data scientists?

**Articles from top bloggers**

- Kirk Borne | Stephanie Glen | Vincent Granville
- Ajit Jaokar | Ronald van Loon | Bernard Marr
- Steve Miller | Bill Schmarzo | Bill Vorhies

**Other popular resources**

- Comprehensive Repository of Data Science and ML Resources
- Statistical Concepts Explained in Simple English
- Machine Learning Concepts Explained in One Picture
- 100 Data Science Interview Questions and Answers
- Cheat Sheets | Curated Articles | Search | Jobs | Courses
- Post a Blog | Forum Questions | Books | Salaries | News

**Archives**: 2008-2014 | 2015-2016 | 2017-2019 | Book 1 | Book 2 | More

**Most popular articles**

- Free Book and Resources for DSC Members
- New Perspectives on Statistical Distributions and Deep Learning
- Time series, Growth Modeling and Data Science Wizardy
- Statistical Concepts Explained in Simple English
- Machine Learning Concepts Explained in One Picture
- Comprehensive Repository of Data Science and ML Resources
- Advanced Machine Learning with Basic Excel
- Difference between ML, Data Science, AI, Deep Learning, and Statistics
- Selected Business Analytics, Data Science and ML articles
- How to Automatically Determine the Number of Clusters in your Data
- Fascinating New Results in the Theory of Randomness
- Hire a Data Scientist | Search DSC | Find a Job
- Post a Blog | Forum Questions

## You need to be a member of Data Science Central to add comments!

Join Data Science Central