Does Your DBMS Support Temporal Data?

Data changes over time, and many applications and users need to access data at different points in time. A traditional DBMS stores data that is implied to be valid now — at the current point-in-time. It doesn’t track the past or future states of the data and the only information you can typically glean from the database is its present state. And for many users and applications the current, up-to-date values for the data are sufficient. But for others, accessing earlier versions of the data is needed. This requirement is known as temporal support. With temportal support it becomes possible to store different database states and to query the data “as of” those different states.

Temporal data stored in the DBMS differs from traditional data in that a time period is attached to the data to indicate when it was valid or changed in the database. Let’s quickly take a look at an example situation where temporal database support might be useful.

Business Time

Consider an insurance company that sells policies to its customers. The terms of any specific policy are valid over a period of time – a “business time.” Over time, the customer can choose to decline further coverage, continue with the existing coverage, or modify the terms of their coverage. So at any specific point in time, the terms of the customer’s policy can differ. Over time, customers will make claims against their policies. This claim information needs to be stored, managed, and analyzed. Accident histories for customers are also important pieces of data with a temporal element.

Now consider the complexity inherent in trying to develop not only a database design that accommodates changing policies, claims, and historical details, but also allows queries such that a user might be able to access a customer’s coverage at a given point in time. In other words, what policies were in effect for that customer as of, say September 11, 2001?

Of course, there are many other types of applications for which temporal support would be useful, it is not just limited to insurance. For example, financial applications, credit history, personnel management, transportation applications, reservation systems, and medical information management all may benefit from maintaining temporal data.

This concept of business time can become quite complex. Consider the situation where a customer has multiple policies that expire on different schedules. Add in the possibility for periods where coverage lapses. And then the database grows in size and the queries become more complex.

With business time temporal support, tables are setup with a period consisting of a beginning time and an end time. The data is valid during that period. There are more details, of course, that can differ from DBMS to DBMS, but the basic gist is that all data in a business time temproal table will have a period setup for which the data is valid. This then, enables queries to be written like this:

select coverage_details
from policy for business_time as of '2011-09-11'
where custno = '000010';

You should be able to see how this is much simpler than trying to implement tables and queries without temporal support.

System Time

There is another aspect of temporal data that needs to be considered, as well. Instead of business time, you also may need to track and manage “system time.” For example, a regulatory mandate may require you to be able to track changes to a piece of personally identifiable information (or PII). Examples of PII include name, phone number, social security number, and driver’s license number (there are many others). Tracking changes to PII is a requirement of many industry and governmental regulations.

A DBMS with system time temporal support can be setup to store every change made to the data. Typically, this is done via a related history table. System time tracking is sometime referred to as data versioning, because every version of the data’s state is stored and can be queried. Support for managing system changes enables users to be able to query the database as of a point in time returning the value of the data as of that timeframe.

The business time indicates the period during which the data is accurate with respect to the world. The system time indicates the period during which the data is stored in the database. These two time periods do not have to be the same for a single piece of data. Suppose, for example, that we wish to store temporal information about 20th century events. The valid business time for this data would be within the range of 1900 and 1999. But if we were to add the information to the database now, perhaps the valid system time for the data would be at some point in 2012. So, of course, you may need to be able to support both business temporal data and system temporal data in the same table. This is called bi-temporal support.

DBMS Support

Many relational DBMS offerings have added temporal support capabilities in recent years. Examples of database systems that have temporal support include IBM Db2, Microsoft SQL Server, Oracle, and Teradata.

The Bottom Line

This overview of temporal support has been short. There are additional capabilities and nuances that need to be understood and digested before you implement temportal databases. For example, deleting data from a temporal table can cause it to have more rows after the delete than before.

So you should learn the temporal capabilities and syntax of the DBMS you are using before you implement temporal tables. And do not go overboard! Define time periods only for those tables with a business or regulatory requirement, and make sure you define your tables appropriately with business and system time periods.

But the bottom line is that applications requiring temporal support can greatly benefit from the efficiency of built-in temporal facilities of your DBMS.

 

 

Of course, you should implement temporal tables with care. Define time periods only for those table with a business or regulatory requirement, and make sure you define your tables appropriately with business and system time periods.

But the bottom line is that applications requiring temporal support can greatly benefit from the efficiency of DB2’s built-in facilities for temporal data.

About craig@craigsmullins.com

I'm a data management strategist, researcher, and consultant with over three decades of experience in all facets of database systems development and implementation.
This entry was posted in DBA, temporal. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s