DM Direct, July 2007
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.
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.
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.
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?
One way to look at it is this. Version Pattern 4 does not permit us to enter versions with a known effective end date. All versions entered are assumed to be valid from their effective begin date "until further notice." So when we enter a version of P138 that becomes effective prior to a version already in the table, we enter it with the understanding that it is effective until further notice, i.e., effective until we take some other action regarding policy P138.
The semantics of a Type 2 proactive insert, one whose effective begin date is prior to the effective begin date of an existing version, violates this understanding. It violates the semantics of proactive activity carried out in the absence of effective end dates because, without any further action being taken with respect to policy P138, the version which was entered first will change its status from being the insertion of a new policy to being an update of an existing policy.
From Mary's point of view, she understood the semantics of her two requests to be this:
Again, the exact same code is used to make both changes. In the first case, Mary got what she wanted. A database update correctly implements her change request. But in the second case, that same code produces a disaster in the making. For two and a half months after the Type 2 update, everything works as it should. Then, without any other database change taking place, on 6/1/04, Mary's policy suddenly becomes an HMO policy.
In the first case, Mary wants a change to something already in effect. In the second case, she wanted a replacement to a request that has not yet been implemented.
We can easily imagine that the business analyst who gathered the requirements for a versioned history of policies failed to distinguish between changes and replacements. After all, there are only three things you can do to rows in a table. You can insert them, update them or delete them. What the user calls a change and what she calls a replacement (or perhaps calls "changing her mind") are, to the business analyst, both physical changes to something already in the database.
In other words, to the business analyst, Mary's changing a policy and Mary's changing her mind amount to a distinction without a difference. As Paul Newman said in Cool Hand Luke, "What we've got here is a failure to communicate."
We need to discuss proactive deletions only briefly. A Type 1 proactive deletion terminates an existing policy. A Type 2 proactive deletion terminates a policy that does not yet exist. But because you can't do anything to something that doesn't exist, we need to say what we mean more clearly than this.
In a non-versioned table, one row represents one object. In a Policy table, each row would represent a policy. But in the Policy Version table, no row represents a policy and each row represents one time slice of one policy. Consequently, actions to the policies themselves do not line up, one for one, with actions to rows in the Policy Version table.
With non-versioned tables, whose rows are one for one with objects, semantics are generally one for one with physical activity. For example, physically inserting a row is semantically inserting an object; physically updating a row is semantically updating an object; and so on. But with versioned tables, none of whose rows represent objects, semantics are not similarly isomorphic with physical activity. For example, physically inserting a row may be semantically inserting an object; but it may not be. Instead, it may be semantically updating an object. And the only physical updates to versioned tables, at least with the version patterns considered so far, are done to implement semantic deletions.
So to speak more clearly about deletions, we should say this: a Type 1 proactive deletion terminates an existing policy by supplying a termination date for the version of the policy that was current at the time of the physical Type 1 action. A Type 2 proactive deletion terminates the version of a policy which has the latest effective begin date, but does so prior to that date. Thus, unless some other action takes place, a Type 2 proactive deletion ensures that its corresponding policy will never become effective.
We will not itemize the semantic constraints which define Version Pattern 4. Enough has already been said that the reader should be able to modify the semantic constraints for Pattern 3 and create a set of constraints that correctly define Pattern 4.
Next time, we will begin our discussion of Version Pattern 5. It is simpler than Pattern 4 in that it is not a bi-temporal pattern. But it is more complex than Pattern 4 in that it drops the non-recurring assumption. Once we have learned to handle objects that appear for awhile, vanish for awhile, and then show up again, and to do so without bi-temporality, we will then re-introduce bi-temporality. That will take us to Version Pattern 6 which is the penultimate versioning pattern.
For more information on related topics, visit the following channels:
©2007 Data Management Review and SourceMedia, Inc. All rights reserved. SourceMedia is an Investcorp company. Use, duplication, or sale of this service, or data contained herein, is strictly prohibited.