Time and Time Again

The major, but by no means exclusive, focus of future columns in this series will be on what I have been calling the "ultimate versioning pattern" but from now on will be calling the "asserted version pattern," or simply "asserted versioning." Following is a brief discussion of the major themes of the asserted versioning approach to managing temporal data.

Seamless access is access to any combination of past, current and future states of persistent objects that are or may be needed, separately or in combination, to support operational decision-making or to complete operational transactions. Expressed in architectural terms, it is access to states of persistent objects that belong in online transaction processing (OLTP) or operational data store (ODS) databases.

Seamless access is fully encapsulated along three dimensions. These are:

1. Query encapsulation. Queries against bi-temporal tables must be simple enough that if a business user could write a query against a non-temporal table, she could also write a query against the corresponding bi-temporal table. This is essential if we are not to return to the "bad old days" in which, for non-production queries or reports, business users had to submit a request to the IT department and usually wait days or weeks for the result. Specifically:

If these queries specify current data they must be identical to queries against corresponding non-temporal tables except for specifying that now falls within both effective and asserted date ranges.
If these queries specify non-current data (data effective and/or asserted, in the past or in the future), they must be identical to queries against corresponding non-temporal tables except for specifying that a past or a future date falls within one or both of those date ranges.
If these queries specify temporal joins, view tables should be used to encapsulate any such joins that otherwise might be difficult for end users to write.
To support all existing queries without modification a current-only view table must exist corresponding to every asserted version table.

2. Transaction encapsulation. Updates to these tables must utilize insert, update and delete transactions that are simple enough to be written by anyone who could write updates against corresponding non-temporal tables. Specifically:

If these updates specify current data, they must be identical to updates against corresponding non-temporal tables.
If these updates specify non-current data, they must be identical to updates against the corresponding non-temporal tables except for specifying the past or future effective and/or assertion dates to be used for the version about to be created.

3. Design encapsulation. Query encapsulation protects the query writer from the complexities of bi-temporality. Transaction encapsulation protects those who write and manage inserts, updates and deletes from those complexities. But what protects the database designer? What protects the data modeler, specifically, the logical data modeler? To add a bi-temporal table to a database, or to change a non-temporal table to a bi-temporal one, what work must these IT professionals do?

There are several considerations. From the point of view of the more complex case, that of changing a non-temporal table to an asserted version table, those considerations include the following:

Adding the columns required to make bi-temporality work.
Modifying the primary key so the database management system (DBMS) can enforce temporal entity integrity (TEI).
Changing all foreign keys to the newly bi-temporal tables to temporal foreign keys (TFKs).
Declaring the temporal correlates of restrict, cascade or set null options.
Enforcing parent-side and child-side temporal referential integrity (TRI) constraints up and down the referential integrity chain for the newly bi-temporal tables.

Specifying structural changes and constraint options will be the job of the logical modeler. Implementing those changes and constraints will be the job of the physical modeler and the implementation team.