Solutions
What is the Solution?
Weis and Johnston have been working on solutions to these problems for over 15 years. When they met in 2005 they discovered that, while they both came from very different backgrounds, they both arrived at a very a similar solution. However, at that time, both patterns were incomplete.
They started collaborating on an ultimate solution that met all of the requirements, easy to implement and performed well. Tom, had the theoretical experience and Randy had the practical. They both were well aware of the pain points, and embarked on perfecting and formalizing the methodology.
The details of their research and solutions are documented in the book Managing Time in Relational Databases, published by Morgan Kaufmann Publishers, an Imprint of Elsevier, Inc.
A summary of their work is documented on this web site, but to really understand the concepts pick up the book at your favorite book retailer or ask for it at your local library. You should also consider bring in a specialist, hopefully certified in designing bi-temporal solutions.
The Relational model does not implicitly support temporal constructs. Theoretically, it supports Referential Integrity of objects at any one given moment in time, but not across time. The relational model needs to be extended to better support these temporal constructs, such as Periods, Temporal Referential Integrity (TRI) and Temporal Entity Integrity (TEI). Asserted Versioning wrangles in constructs and maintains the data and temporal integrity.
We will provide a summary of the solutions here to help get you pointed in the right direction.
Summary
There are 5 main components to the solution:
- The bi-temporal pattern
- A reusable framework, such as the AVF
- Code to maintain Temporal Entity Integrity and Temporal Referential Integrity
- A schema and code generator to build the schema and and maintain the data
- An easy to use API to the framework and code generator
Now to do this you need a good understanding of the semantics of assertions and versions, and how the underlying data needs to be maintained
The managed objects of asserted versioning are objects, episodes, versions and assertions. A table represents a kind of thing – a policy, a customer, a product, and so on. In a conventional table, each row represents a different instance of its kind – a different policy, a different customer, a different product, etc. But in a temporal table, multiple rows can represent the same object, each one representing that object during some time period of its existence. Each of these rows is a version of the object it represents.
Breaking the one-to-one correspondence between objects and rows means that the most semantically fundamental managed object in a temporal table is not a row. It is a group of temporally contiguous rows, a group that we call an episode of that object. The implications for relational theory are significant because relational theory does not recognize the need for a semantic object whose instances are contained in tables and which in turn contain rows.
In addition to objects, episodes and versions, asserted versioning introduces the concept of an assertion. An assertion is a claim that a row we have inserted into an asserted version table makes a true statement about what the object it represents is like during the time period it represents it. Significantly, bi-temporal tables may contain rows that represent assertions we are no longer willing to make, rows that we have come to realize are not true statements and never were. Just as significantly, bi-temporal tables which are asserted version tables may also contain rows that we are not yet ready to assert.
Temporal constraints are the temporal analogues of entity integrity and referential integrity. Temporal entity integrity is entity integrity applied to temporal tables; but we need to understand what "entity integrity applied to temporal tables" means. Temporal referential integrity is referential integrity in which the parent table is a temporal table, and the parent object is an episode. But again, we need to understand what this means.
Framework
Many organizations try to implement temporal structures and maintain temporal constraints one table at a time. This is not a trivial task, even for one table, let alone 200, 500 or 1,000 tables. Therefore, we recommend developing a reusable framework that has a simple to use and understand API. Develop the framework once, and then use it for any and all objects that need temporal integrity.
The closer the framework looks to normal relational SQL queries the easier it will be for programmers to use and adapt. The AVF can convert a non-temporal database to a temporal database without having to change any program code. By default all assertions are as of Now(), as are effective begin dates. Then, with simple extensions, any valid assertion and effective period can be included in Inserts, Updates, Deletes and Selects.
Temporal Entity Integrity (TEI)
Temporal integrity constraints govern the effective time relationships among bi-temporal rows. But, as we pointed out earlier, these effective time relationships apply only within shared assertion time. For example, when one version is asserted from January 2012 to 12/31/9999, and another version of the same object is asserted from March 2012 to 12/31/9999, then the effective time periods of those two versions must not [intersect] from March 2012 to 12/31/9999 in assertion time. But from January to March, they neither intersect nor do not intersect. During those two months, the comparison doesn't apply. During that time, those two versions are what philosophers call "incommensurable".
A unique Index on a non-temporal table, such as on a primary key or a unique alternate key will guarantee uniqueness. For a temporal table, this uniqueness must be maintained by the framework code. If there is a collision in effective and assertion time periods, temporal entity integrity checks will catch it. Asserted Versioning uses an Object ID (OID) as its Temporal Primary Key (TPK). The framework guarantees uniqueness across the effective and asserted time periods. The DBMS allows duplicates, but the framework maintains TEI.
Temporal Referential Integrity (TRI)
Referential integrity (RI) reflects an existence constraint. If a child row is RI dependent on a parent row, this is based on the fact that the object represented by the child row is existence-dependent on the object represented by the parent row. Therefore, a child row cannot be inserted into the database unless its referenced parent row is already present, and that parent row cannot be deleted from the database as long as any child row referencing it is present.
A Parent Temporal Table and a Child Temporal Table. Finally, we consider the case in which both the parent and the child tables are temporal tables. In this case, the foreign key in the child table is a TFK and the assertion and effective time periods of every row in the child table whose TFK references an object X must be fully overlapped by the corresponding time periods of an episode of X in the parent table.
Parent-Side Temporal Referential Integrity. Of course, TRI, like RI, can be violated from the parent side as well. In the case of TRI, a violation would occur if there were a child row dependent on a parent episode, and a modification to that parent episode reduced the time period covered by the episode so that it no longer fully overlapped the time period of the child row. Conceptually, shortening the time period of an episode with dependent child rows (either versions or rows in non-temporal tables) so that the parent episode no longer fully overlaps the time period of one or more of those child rows, is like a deletion in a conventional parent table in that there are three options for handling it. First, we may want to simply block the transaction and prevent the shortening from taking place. Second, we may want to permit the transaction to proceed, but find all the dependent child rows and set their temporal foreign keys to the parent object to null. Or, finally, we may want to delete all child rows in dependent conventional tables, and withdraw either all TRI-dependent versions in child temporal tables, or all episodes containing one or more TRI-dependent versions.
What is the problem?