Some applications need to design and build databases where information changes over time. Doing so without temporal table support is possible, although 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 rows in the Policy table became valid. Without temporal table support, one approach that the application can take is to add a DATE column to the Policy table called Start_Date. Suppose the application also needs to know when rows in the table are no longer valid. Another DATE column called End_Date can accomplish this.
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);
Several complications are now evident. 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 needs to be updated to reflect when the policy conditions were changed.
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 changing the Start_Date and End_Date columns. Queries will be more complicated.
The mere presence of a DATE column 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.
Rather than using approaches such as adding DATE columns to traditional tables, Vantage provides built-in support to more effectively create, query, and modify time-varying tables.