One objective of this book is to describe how to manage uni-temporal and bi-temporal data in relational databases in such a way that they can be seamlessly accessed together with current data. ¹ By “seamlessly” we mean (i) maintained with transactions simple enough that anyone who writes transactions against conventional tables could write them; (ii) accessed with queries simple enough that anyone who writes queries against conventional tables could write them; and (iii) executed with performance similar to that for transactions and queries that target conventional data only.

A second objective is to describe how to encapsulate the complexities of uni-temporal and bi-temporal data management. These complexities are nowhere better illustrated than in a book published ten years ago by Dr. Richard Snodgrass, the leading computer scientist in the field. In this book, Developing Time-Oriented Database Applications in SQL (Morgan-Kaufmann, San Francisco, 2000), Dr. Snodgrass provides extensive examples of temporal schemas and also of the SQL, for several different relational DBMSs, that is required to make uni- and bi-temporality work, and especially to enforce the constraints that must be satisfied as temporal data is created and maintained. Many of these SQL examples are dozens of lines long, and quite complex.

This is not the kind of code that should be written over and over again, each time a new database application is developed. It is code that insures the integrity of the database regardless of the applications that use that database. And so until that code is written by vendors into their DBMS products, it is code that should exist as an interface between applications and the DBMS that manages the database—a single codebase used by multiple applications, developed and maintained independently of the applications that will use it. A codebase which plays this role is sometimes called a data access layer or a persistence and query service framework.

So we have concluded that the best way to provide temporal functionality for databases managed with today's DBMSs, and accessed with today's SQL, is to encapsulate that complexity. Asserted Versioning does this. In doing so, it also provides an enterprise solution to the problem of managing temporal data, thus supporting both the semantic and physical interoperability of temporal data across all the databases in the enterprise.

Asserted Versioning encapsulates the design, maintenance and querying of both uni-temporal and bi-temporal data. Design encapsulation means that data modelers do not have to design temporal data structures. Instead, declarative specifications replace that design work. These declarations specify, among other things, which entities in a logical data model are to become bi-temporal tables when physically generated, which column or columns constitute business keys unique to the object represented, and between which pairs of tables there will exist a temporal form of referential integrity.

Maintenance encapsulation and query encapsulation mean, as we indicated earlier, that inserts, updates and deletes to bi-temporal tables, and queries against them, are simple enough that anyone who could write them against non-temporal tables could also write them against Asserted Versioning's temporal tables. Maintenance encapsulation, in the Asserted Versioning Framework (AVF) we are developing, is provided by an API, Calls to which may be replaced by native SQL issued directly to a DBMS once temporal extensions to SQL are approved by standards committees and implemented by vendors. ² Functioning in this way as a persistence framework, what the AVF persists is not simply data in relational tables. It persists both assertions and versions, and it enforces the semantic constraints between and among these rows which are the temporal analogues of entity integrity and referential integrity.

Functioning as a query service framework, Asserted Versioning provides query encapsulation for access to current data by means of a set of views which allow all queries against current data to continue to work, without modification. Query encapsulation is also provided for queries which need seamless access to any combination and range of past, present and future data, along either or both of two temporal dimensions. With asserted version tables guaranteed to contain only semantically well-formed bi-temporal data, queries against those tables can be remarkably simple, requiring only the addition of one or two point or period of time predicates to an otherwise identical query against current data.

A third objective of this book is to explain how to implement temporal data management as an enterprise solution. The alternative, of course, is to implement it piecemeal, as a series of tactical solutions. With tactical solutions, developed project by project for different applications and different databases, some will support temporal semantics that others will not support. Where the same semantics are supported, the schemas and the code that support them will usually be different and, in some cases, radically different. In most cases, the code that supports temporal semantics will be embedded in the same programs that support the application-specific semantics that have nothing to do with temporality. Federated queries, attempting to join temporal data across databases temporalized in different ways by different tactical solutions, will inevitably fail. In fixing them, those queries will often become several times more complex than they would have been if they had been joining across a unified enterprise solution.

Asserted Versioning is that enterprise solution. Every table, in every database, that is created as an asserted version table, or that is converted into an asserted version table, will support the full range of bi-temporal semantics. A single unit of code—our Asserted Versioning Framework (AVF), or your own implementation of these concepts—will support every asserted version table in every database.

This code will be physically separate from application code. All logic to maintain temporal data, consequently, will be removed from application programs and replaced, at every point, by an API Call to the AVF. Federated queries against temporal data will not need to contain ad hoc manipulations whose sole purpose is to resolve differences between different implementations of the same temporal semantics, or to scale a more robust implementation for one table down to a less expressive one for another table.

As an enterprise solution, Asserted Versioning is also a bridge to the future. That future is one in which temporal functionality will be provided by commercial DBMSs and directly invoked by SQL transactions and queries. ³ But Asserted Versioning can be implemented now, at a pace and with the priorities chosen by each enterprise. It is a way for businesses to begin to prepare for that future by removing procedural support for temporal data from their applications and replacing it with declarative Call statements which invoke the AVF. Hidden behind API Calls and views, the eventual conversion from Asserted Versioning to commercially implemented solutions, if the business chooses to make that conversion, will be nearly transparent to the enterprise. Most of the work of conversion will already have been done.

But other migration strategies are also possible. One is to leave the AVF in place, and let future versions of the AVF retire its own code and instead invoke the temporal support provided by these future DBMSs, as vendors make that support available. As we will see, in particular in Chapters 12, 13 and 16, there is important bi-temporal functionality provided by Asserted Versioning that is not yet even a topic of discussion within the computer science community. With the Asserted Versioning Framework remaining in place, a business can continue to support that important functionality while migrating to commercial implementations of specific temporal features as those implementations become available, and it can do this without needing to modify application code.

The final objective of this book is to describe how to bring pending transactions into the production tables that are their targets, and how to retain posted transactions in those same tables. Pending transactions are insert, update and delete statements that have been written but not yet submitted to the applications that maintain the production database. Sometimes they are collected outside the target database, in batch transaction files. More commonly, they are collected inside the target database, in batch transaction tables. Posted transactions, as we use the term, are copies of data about to be inserted, and before-images of data about to be updated or deleted.

Borrowing a metaphor common to many logistics applications, we think of pending transactions as existing at various places along inflow pipelines, and posted transactions as data destined for some kind of logfile, and as moving towards that destination along outflow pipelines. So if we can bring pending transactions into their target tables, and retain posted transactions in those same tables, we will, in terms of this metaphor, have internalized pipeline datasets. ⁴

Besides production tables, the batch transaction files which update them, and the logfiles which retain the history of those updates, production data exists in other datasets as well. Some production tables have history tables paired with them, in which all past versions of the data in those production tables is kept. Sometimes a group of rows in one or more production tables is locked and then copied to another physical location. After being worked on in these staging areas, the data is moved back to its points of origin, overlaying the original locked copies of that data.

In today's world of IT data management, a great deal of the Operations budget is consumed in managing these multiple physical datasets across which production data is spread. In one sense, that's the entire job of IT Operations. The IT Operations schedule, and various workflow management systems, then attempt to coordinate updates to these scattered datasets so those updates happen in the right sequence and produce the right results. Other tools used to insure a consistent, sequenced and coordinated set of production data across the entire system of datasets and pipelines, include DBMS triggers associated with various pre-conditions or post-conditions, asynchronous transaction managers, and manually coordinated asynchronous feeds from one database to another.

These processes and environments are both expensive to maintain and conducive to error. For example, with history tables, and work-in-progress in external staging areas, and a series of pending transaction datasets, a change to a single semantic unit of information, e.g. to the policy type of an insurance policy, may need to be applied to many physical copies of that information. Even with triggers and other automated processes to help, some of those datasets may be overlooked, especially the external staging areas that are not always there, and so are not part of regularly scheduled maintenance activity. If the coordination is asynchronous, i.e. not part of a single atomic and isolated unit of work, then latency is involved, a period of time in which the database, or set of databases, is in an inconsistent state. Also, error recovery must take these interdependencies into consideration; and while the propagation of updates across multiple datasets may be partially or completely automated, recovery from errors in those processes usually is not, and often requires manual intervention.

This scattering of production data also affects those who write queries. To get the information they are looking for, query authors must know about these scattered datasets because they cannot assume that all the data that might be qualified by their queries is contained in one place. Across these datasets, there are differences in the life cycle stage of the various datasets (e.g. pending transactions, posted transactions, past, present or current versions, etc.). Across these datasets, there will inevitably be some level of redundancy. Frequently, no one table will contain all the instances of a given type (e.g. all policies) that are needed to satisfy a query.

Think of a world of corporate data in which none of that is necessary, a world in which all pipeline datasets are contained in the single table that is their destination or their point of origin. In this world, maintaining data is a “submit it and forget it” activity, not one in which maintenance transactions are initially created, and then must be shepherded through a series of intermediate rest and recuperation points until they are eventually applied to their target tables. In this world, a query is never compromised because some source of relevant data was overlooked. In this world, production tables contain all the data about their objects.