Management of temporal data by means of a canonical schema

A computer program embodied in a computer-readable storage medium for managing temporal data, the computer program comprising:

• logic adapted to store temporal data in at least one temporal table, the at least one temporal table being stored in a temporal database, each temporal table using a single canonical schema that includes, in any order:
• at least one surrogate-valued column that designates an object identifier of an object represented in a row of the respective temporal table;
• and
• at least one time-valued column that designates an effective-time period that indicates:
• a period of time in the past during which the object represented by the row existed in the state described by business data in the row, if the effective-time period ended prior to a present moment,
• a current period of time during which the object represented by the row exists in the state described by the business data in the row, if the effective-time period contains the present moment, and
• a period of time in the future during which the object represented by the row will exist in the state described by the business data in the row, if the effective-time period begins after the present moment;
• logic adapted to store additional data in the at least one temporal table, the additional data comprising at least one business data column such that each business data column in each row in the temporal table contains data that describes the state of the object represented by the row in which each business data column occurs;
• and
• logic adapted to create temporal tables, the logic adapted to create temporal tables including, in any order:
• a preliminary data model;
• a first set of metadata that enables identification of one or more precursor tables in the preliminary data model, the first set of metadata comprising, for each identified precursor table, the name of the temporal table for which the precursor table is an otherwise identical non-temporal table;
• a second set of metadata that enables identification of, for each temporal table generated from a precursor table designated in the first set of metadata, each temporal referential integrity relationship in which that temporal table is a child temporal table, the second set of metadata comprising, for each of the temporal referential integrity relationships, in any order:
• logic adapted to identify the at least one column having the temporal foreign key that expresses the temporal referential integrity relationship;
• logic adapted to identify a parent temporal table for the temporal foreign key;
• logic adapted to designate whether the temporal foreign key is optional or required;
• and
• logic adapted to designate whether a temporal delete transaction applied to one or more rows in the parent temporal table should be, if there are one or more rows in the child temporal table that are temporal referential integrity-related to the one or more rows in the parent temporal table about to be deleted and which are within the target space of that temporal delete transaction, restricted, or processed so that the temporal foreign key in the rows in the child temporal table that are temporal referential integrity-related to the one or more rows in the parent temporal table about to be deleted and which are within the target space of that temporal delete transaction are set null, or processed so that the temporal delete transaction is cascaded to the rows in the child temporal table which are temporal referential integrity-related to the one or more rows in the parent temporal table about to be deleted and which are within the target space of that temporal delete transaction, the cascade to apply recursively;
• a third set of metadata that enables identification of, for each temporal table corresponding to a precursor table identified by the first set of metadata, the columns that include the business key of the temporal table, the columns having the same datatype and corresponding, one-for-one and in sequence, to the columns comprising the primary key of the corresponding precursor table;
• a fourth set of metadata that defines a one-to-one correspondence between the at least one column that includes a foreign key in a precursor table and the at least one column which comprises the corresponding temporal foreign key in the corresponding temporal table;
• and
• logic adapted to generate the temporal database from the preliminary data model and the sets of metadata such that for each precursor table that is identified by the first set of metadata, the table generated in the temporal database is a temporal table for which the precursor table from which it has been generated is an otherwise identical non-temporal table;
• whereby a cost to express temporal data requirements in data models is reduced;
• and
• whereby no changes are required to the data models of databases some or all of whose tables are converted into temporal tables.

A computer program embodied in a computer-readable storage medium for managing temporal data, the computer program comprising:

• logic adapted to store temporal data in at least one temporal table, the at least one temporal table being stored in a temporal database, each temporal table using a single canonical schema that includes, in any order:
• at least one surrogate-valued column that designates an object identifier of an object represented in a row of the respective temporal table;
• and
• at least one time-valued column that designates an effective-time period that indicates:
• a period of time in the past during which the object represented by the row existed in the state described by business data in the row, if the effective-time period ended prior to a present moment,
• a current period of time during which the object represented by the row exists in the state described by the business data in the row, if the effective-time period contains the present moment, and
• a period of time in the future during which the object represented by the row will exist in the state described by the business data in the row, if the effective-time period begins after the present moment;
• logic adapted to store additional data in the at least one temporal table, the additional data comprising at least one business data column such that each business data column in each row in the temporal table contains data that describes the state of the object represented by the row in which each business data column occurs;
• logic adapted to create and maintain rows in temporal tables that have assertion-time periods in the future, the logic adapted to create and maintain rows comprising:
• logic adapted to apply deferred temporal insert, deferred temporal update, and deferred temporal delete transactions against the temporal database by storing the temporal data resulting from the deferred temporal insert, the deferred temporal update, and the deferred temporal delete transactions as deferred assertions in one or more of the temporal tables contained in the temporal database, the deferred assertions having an assertion begin date equal to a future point in assertion time specified on the deferred temporal transaction;
• logic adapted to extend the constraints of temporal entity integrity and temporal referential integrity to apply to temporal data existing in future assertion time;
• logic adapted to move deferred assertions nearer in assertion time to the present moment;
• and
• logic adapted to move deferred assertions farther in assertion time from the present moment;
• wherein the deferred assertions are rows of data that will not be claimed to make a true statement unless and until their assertion-time periods become current;
• logic adapted to manage collections of maintenance transactions by asynchronous transaction processing;
• and
• logic adapted to contain work-in-progress modifications to one or more sets of assertions in a temporal table in the same physical table as production data and the same physical table that contains other non-deferred categories of temporal data;
• and
• logic adapted to improve performance of queries that utilize a temporal index and that request rows of data that exist in current assertion time, the logic adapted to improve the performance of queries comprising:
• a match predicate added to the temporal index, the match predicate having, for each row in the temporal table, one of two values, one of the two values indicating that the row has an assertion-time period that is in the past, and the other of the two values indicating that the row is not known to have an assertion-time period that is in the past;
• and
• logic adapted to enhance the retrieval of rows of data that have an assertion-time period containing the present moment, wherein the value of the temporal index to speed up is enhanced by filtering out known past rows when looking for current rows, in comparison to otherwise identical indexes that employ one or more columns of data that can only be used as range predicates.

A computer program for managing temporal data, embodied in a computer-readable storage medium, the computer program comprising:

• logic adapted to store temporal data in at least one temporal table, the at least one temporal table being stored in a temporal database, each temporal table using a canonical schema that includes, in any order:
• at least one surrogate-valued column that designates a unique object identifier of an object represented in a row of the at least one temporal table;
• at least one time-valued column that designates a valid-time period that indicates:
• a period of time in the past during which the object represented by the row existed in a prior state, if the valid-time period ended prior to a present moment,
• a current period of time during which the object represented by the row exists in a current state, if the valid-time period contains the present moment, and
• a period of time in the future during which the object represented by the row will exist in a future state, if the valid-time period begins after the present moment;
• and
• at least one time-valued column that designates a transaction-time period that indicates:
• a period of time in the past during which the row was designated to be correct, if the transaction-time period ended prior to the present moment, and
• a current period of time during which the row is designated to be correct, if the transaction-time period contains the present moment,
• the canonical schema including a same set of columns for supporting the creation and management of data in at least one of valid-time, transaction-time, and bi-temporal time.