Is there any database management system (DBMS) which supports temporal databases?


Chuck Kelley's Answer: None that I am aware. However, there are lots of research papers and articles that discuss how to implement temporal within relational databases.

Tom Haughey's Answer: I am not completely versed on the new vendor products in this area but here is my general thinking. Currently, there are two vendor approaches to temporal databases: build a new DBMS or extend an existing DBMS. In terms of extending an existing DBMS, there are two approaches: extend a relational DBMS (RDBMS) or extend an object oriented DBMS (ODBMS). RDBMS are very oriented toward processing high volume, short-lived transactions, such as you see in financial transactions, whereas ODBMSs are oriented more toward complex objects and long-lived transactions, such as you see in GIS and spatio-temporal applications. The ODBMSs in my opinion are not capable of handling large volumes of transactions such as you seen in order processing or financial processing, or large volumes of queries (involving huge amounts of data) such as you see in data warehousing. That is the domain of the RDBMS. On the other hand, because the cost to build a new DBMS is huge, in the short-term I would look to existing DBMSs and to temporal extensions in the DBMS and SQL that existing major vendors have to offer. Each of the major vendors (IBM, Oracle, Sybase, etc.) have some offerings in these areas.

The design of temporal databases using conventional DBMSs is becoming more and more prominent. Even though technical features to support temporal data already exist in many products, it is my experience that they are not frequently used in mission-critical applications. Typically, time is handled directly in the design in one of several ways: by qualifying existing tables with time or by setting up separate tables to collect history. Qualifying existing tables means not just adding time but making time part of the natural key (whether you use a surrogate key physically is another matter). Setting up separate tables for time can be done in both OLTP and in data warehousing. Developers then handle temporal operations through SQL or a BI query tool. A true temporal DBMS has time as an inherent part of the architecture of the product and supports a fully temporal version of SQL. See the writings of Richard T. Snodgrass and Chris Date for more details on these. Snodgrass has been writing on this subject for years. Fasten your seat belt before you read them (especially Date) because it is not bedtime reading.

Given that most data warehouses, data marts and other BI applications are temporal in nature, one would have predicted a much greater use of advanced DBMS capabilities today. There are several reasons that temporal DBMS extensions haven't been used more widely. One is that it is not always a safe bet to be on the leading edge of such technologies due to the risk involved. Second, we have established practices for handling time/audit in existing OLTP applications and for dimensioning time in data warehouses. In addition, both RDBMS and multidimensional DBMSs (MDDBMS) can handle data warehouse history needs very well. In these, time becomes one of the dominant dimensions, even in the form of one or more calendars. Facts are inherently historical. As new facts happen, including changes to existing facts, new fact rows are entered. The time dimension, and time varying dimensions, works well for regularly scheduled data loads in which the data warehouse is refreshed daily, weekly or even monthly. It can even be used to support rolling summaries in the warehouse. Recently, there is a trend toward near real-time warehousing and to operational data stores. In these, feeds occur at very short intervals or even nearly synchronous with operational transactions and are often done through message brokers rather than standard ETL. In these situations, there might be a need to keep some amount of history where updates are volatile and unpredictable, which cannot readily be represented using a dimension table. This will be the specific domain of temporal databases, when they fully arrive.

One last point. Time-series data is another temporal subject. History is usually considered as the random occurrence of heterogeneous data and events, such as you see in customer changes, customer orders and order changes, etc. A time-series, on the other hand, is the regular collection of specific attributes at a regular interval. Though the data warehouse is sometimes called a "time-series," in reality is it partly a time series and partly history. Aggregates and periodic statuses, both important grains, represent time-series, such as customer by product by month, or policy monthly status. The changes to an order or to a customer are more historical; they can happen in any way at any time. Look at it this way. Every day every stock on the NYSE has a closing price, but you don't have to buy any stock on any given day. Closing prices are a time-series; stock trading is historical. Some existing DBMSs offer time-series extensions, plus there are specific time-series DBMSs. I believe that it is safer to consider temporal DBMSs and time-series DBMS as niche DBMSs, used for specific purposes, and to continue to use existing robust DBMSs and their extensions (together with established database design techniques) for mainstream applications.

Joe Oates' Answer: A temporal DBMS is one that comes with built-in time aspects, e.g., a temporal data model and a temporal version of structured query language. The goal of a temporal DBMS is to provide temporal aspects that usually include the concept of the time period in which a fact is true (valid-time) as well as the time period when the transaction was actually recorded and believed to be true in the database (transaction-time). These attributes are combined in a temporal DBMS to provide the ability to record bitemporal data which is the combination of both of valid-time and transaction-time.

I don't know of any commercially popular relational DBMS that supports the temporal aspects of a structured query language (e.g., "overlaps," etc.). The above concepts, while very important to BI analytics, were not important to transactional processing for which most popular DBMS systems were designed.

However, a properly designed BI database can simulate temporal DBMS features very well. The only caveat is that "normal" SQL statements must be used.

For example, consider if John Smith was a product manager for widgets from October 1, 2002, until December 31, 2004. Let's also say that the fact that John Smith started being the widget product manager was entered on October 3, 2002, with the "start date" recorded as October 1, 2002, and the "end date" with no entry because at the time that John Smith was appointed, no one knew how long he would be the product manager.

Let's also say that when John Smith became product manager for widgets the price was $12 per widget, and this was true until November 1, 2004. So, the original record for Widgets having the price of $12 had the "start date" recorded as July 1, 2001, which was entered on July 5, 2001, and the "end date" had no entry.

When the price was changed from $12 per widget to $13, it became necessary to update the above record by entering October 31, 2004, in the "end date." This update was recorded on November 7, 2004. Additionally, a new record was added for widgets that contained the new price of $13, the "start date" for this price was November 1, 2004, the "end date" had no entry, and the new record was entered on November 8, 2004.

On January 1, 2005, someone else took over as widget product manager from John Smith. Therefore, the record that recorded John Smith being the product manager for widgets will now have to be updated with a valid "end date" and the date on which the record was updated.

This simple example allows queries to be written that can tell us, among other things:

  • When John Smith was the widget product manager and how long he held this position;
  • That John Smith was the widget product manager when widgets were $12 (his time being the widget product manager overlapped the time in which widgets were $12);
  • That anyone querying the database on November 3, 2004, would not have known that the price for Widgets had been increased to $13.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access