Some applications need to design and build databases where information changes over time. Doing so without temporal table support is possible, though complex.
Consider an application for an insurance company that uses a Policy table where the definition looks like this:
CREATE TABLE Policy( Policy_ID INTEGER, Customer_ID INTEGER, Policy_Type CHAR(2), Policy_Details CHAR(40) ) UNIQUE PRIMARY INDEX(Policy_ID);
Suppose the application needs to record when policies, represented by rows in the Policy table, became valid. One approach is to add a DATE type column to the Policy table called Start_Date. If the application also needs to know when policies are no longer valid, a corresponding End_Date column can be added.
The new definition of the table looks like this:
CREATE TABLE Policy( Policy_ID INTEGER, Customer_ID INTEGER, Policy_Type CHAR(2), Policy_Details CHAR(40) Start_Date DATE, End_Date DATE ) UNIQUE PRIMARY INDEX(Policy_ID);
However, this introduces several complications. For example, if a customer makes a change to their policy during the life of the policy, a new row would need to be created to store the new policy conditions that are in effect from that time until the end of the policy. But the policy conditions prior to the change are also likely to be important to retain for historical reasons. The original row represents the conditions that were in effect for the beginning portion of the policy, but the End_Date on the original row now needs to be updated to reflect when the policy conditions were changed, rather than when the policy becomes invalid.
Additionally, because of these types of changes, it becomes likely that more than one row now has the same value for Policy_ID, so the primary index for the table also needs to change. All modifications to the table must now consider explicitly changing the Start_Date and End_Date columns. Queries become more complicated.
The mere presence of one or more DateTime type columns in a table does not make the table a temporal table nor make the database a temporal database. A temporal database must record the time-varying nature of the information managed by the enterprise.
Vantage provides built-in support for ANSI-compatible temporal tables. Temporal variations of standard SQL statements let you create, alter, query and modify data that changes over time. Queries and modifications can include temporal qualifiers that reference a time dimension and act as criteria or selectors on the data. They affect only the data that meets the time criterion.
Temporal columns and temporal statements facilitate creating applications that can represent information that changes over time.