Time and Time Again: Managing Time in Relational Databases, Part 7: Completing Version Pattern 4

InfoManagement Direct, July 2007

Tom Johnston, Randall Weis

As we saw in Part 6, what distinguishes Pattern 4 from Pattern 3 is bi-temporality. With Pattern 4, the business effectivity begin date of a version can be different than the date the version was physically inserted into its table. This was not possible with Pattern 3.

Consequently, bi-temporality makes it possible to physically insert a row representing a new policy proactively, prior to the date the policy becomes effective. As long as queries are careful to filter out rows whose effectivity lies in the future (unless those queries are expressly written to retrieve such rows), the ability to proactively insert new objects is often very useful. With this ability, we can "insert and forget," meaning that we can insert a new object as soon as we know when it will become effective, and then not have to do anything later on to "activate" it. The mere passage of time will ensure that the object becomes a "now in effect" object on the indicated effectivity date.

By the same token, bi-temporality also makes it possible to physically insert a row representing a new policy retroactively, after the date the policy becomes effective. We concluded Part 6 by discussing why retroactive inserts were semantically invalid, and why they must therefore be prevented. A retroactive insert of a new object, n days after its business effective begin date, is semantically invalid because it changes the past. Prior to the insert, a query about any point in time within n days prior to the time of insert, would show that the object did not then exist. After the insert, the same query would show that the object did exist at that point in time. One query, contradictory results.

Let's turn now to the two other scenarios we have been using to illustrate all of these version patterns - updates and deletes.

Scenario 4.2a: A Type 1 Proactive Update

On 1/12/04, policy P138 is created for Mary Jaspers. As we have already described, the creation of a new policy is implemented by inserting an initial version for that policy into the Policy Version table.

However, policy P138 does not become immediately effective. It becomes effective on 6/1/04. It is, thus, a proactive insert of a policy. Since inserting a new policy and updating an existing policy are both implemented by physically inserting a row in the Policy Version table, proactive updating of a policy should follow the same pattern as proactive insertion of a policy. Proactive deletion should be similarly straightforward. But as we shall see, things are a little more complicated than that.

While there is only one type of proactive insertion of a new policy, there are two types of proactive updates. With the first type, Mary requests a change to her policy to become effective after the policy itself becomes effective. With the second type, Mary requests a change to her policy to become effective after it is entered into the database, but prior to its effective date.

To illustrate a Type 1 proactive update, let's assume that on 6/18/04, Mary requests a change in policy type from PPO to HMO, to become effective on 9/1/04, and that this change is physically recorded in the Policy Version table on 7/1/04. This is indeed a proactive update since the create date is two months prior to the effectivity date.

After inserting the new version (a physical insert which semantically updates the policy), the Policy Version table looks like this:

What these two rows tells us is semantically straightforward. Policy P138 goes into effect on 6/1/04, as a PPO policy. Three months to the day later, it remains in effect but becomes an HMO policy.

Scenarios 4.2b: a Type 2 Proactive Update

But there is a second kind of proactive update to consider. What will the Policy Version table look like if, instead of the update just illustrated, we instead enter an update on 3/10/04, which also changes P138 from PPO to HMO, but this time becoming effective on 3/15/04?

Clearly this is also a proactive update, since it is entered prior to its effectivity. So shouldn't things be just as straightforward as with the Type 1 update? Let's see.

After inserting the new version (a physical insert which semantically updates the policy), the Policy Version table looks like this:

What these two rows tell us may be semantically straightforward, but it is also wrong! In other words, the result is not what we intended. These two rows tell us that Policy P138 will go into effect on 3/15/04, as an HMO policy, and that two and a half months later, it will remain in effect but will become a PPO policy.

Type 1 and Type 2 Proactive Updates. What's the Difference?

The difference between Type 1 and Type 2 proactive updates does not lie in the update transactions themselves. We may assume that the schema for the transactions is the same in both cases. Neither does the difference lie in the code processing the two transactions. We may assume that it is the same code.

What, then, is the difference? Why do Type 1 proactive updates "make sense," while Type 2 proactive updates do not?

The relevant difference between these two types of proactive update seems to be that Type 1 is an update to a policy that is already in effect, i.e., that has a version in effect at the time the new version is physically inserted. Type 2, however, is an update to a policy that has not yet gone into effect.

But how does this difference result in the difference between a semantically correct database state and a semantically incorrect one?

Page 1 of 2.