DM Direct, June 2007
The business requirement for Version Pattern 3 is to be able to show what an object was like at any point in its lifetime. To do this, we must retain the before-update state of an object, not just the after-update state. We must retain a versioned history of changes to the state of an object. This means that in place of the Policy table we have been using thus far, we will need a Policy Version table.
{BR-3}. Support {BR-2}, and also retain a queryable history of changes to objects.
The primary key of our original Policy table is policy-nbr. So the primary key of our Policy Version table must be policy-nbr plus something else. The purpose of that something else is to distinguish each version of a policy from all other versions of that same policy.
Pretty much anything will do to distinguish policy versions. In fact, any value guaranteed not to recur for the same policy will do. The first thing that comes to mind is a sequence number. The initial version is 1, and the subsequent n versions are 2, 3, ...... , n.
This isn't a bad idea, just as the logical delete flag, discussed in Version Pattern 2, was not a bad idea. But just as we preferred a delete date to a delete flag, we prefer to use a date as our version-discriminating part of the primary key for policy versions, and thus the combination of policy-nbr and this date to uniquely identify each row in the Policy Version table.
Version Pattern 3 does not distinguish business effectivity dates from physical database activity dates. We must have a business effectivity date, of course. But for the purposes of this analysis, we can consider this date as either the business effectivity start date for the version, or that date and also the date the version was physically inserted. In the former case, we simply fail to record the date of physical database activity. In the latter case, the two dates are identical, with complications that we will examine later.
The former case is simpler, but it is also almost never seen in production databases. Physical insertion dates (and physical last update dates, in the case of updates in place) are almost always included. So for Version Pattern 3, we will assume that version begin date is both the date the inserted row became business effective and also the date the row was physically inserted.
Let's now recreate the series of events described in Scenario 1. We begin with the event that created a new policy for Mary Jaspers. This event takes place on 1/12/04.
As we have already pointed out, the physical action of inserting the initial version for a policy is also the logical action of inserting a new policy. Here is the result of inserting Mary's policy effective 1/12/04:
Figure 1: Result of Inserting Mary Jaspers's Policy Effective 1/12/04
The primary key of the policy version is policy-nbr (the unique identifier for a policy) plus version begin date. All versions for the same policy have the same policy number and are distinguished from one another by their begin date. Because Version Pattern 3 is based on the assumption that objects cannot recur (as described in Part 4), there will be no temporal gaps among versions for the same object. Consequently, we also know the end date for all versions but the current one. It is one day (one clock tick) prior to the begin date for the chronologically next version.
If the above row were inserted on any date later than 1/12/04, it would be a retroactive insert. If it were inserted on any date earlier than 1/12/04, it would be a proactive insert. But as long as rows must be inserted on their business effectivity dates, then neither retroactive nor proactive inserts are possible. In Version Pattern 4, we will distinguish these two kinds of dates and consider retroactive and proactive database activity.
Using this primary key presupposes that we can never have two versions of the same policy that begin on the same date, i.e., on the same tick of whatever clock we are using. For now, we'll stick with this assumption. Later on, we will examine an exception to it.
Figure 2: Timeline Diagram for Scenario 3.1
By inserting this initial version, we have created policy P138, effective 1/12/2004. Now let's see how the first update to this policy is represented in the Policy Version table.
On 3/14/05, Mary changes her policy type from PPO to HMO (Health Maintenance Organization). After that change is recorded in the Policy Version table, the table looks like this:
Figure 3: Policy Version Table After Policy Type Change
Because of our no-gap assumption, we know from this data that this policy was a PPO policy from 1/12/04 through 3/13/05 - the latter date being one clock tick prior to the begin date of the next version.
Note: One way of representing date ranges is by a pair of dates (one of them inferred, in this example). Another is to specify a begin date and a duration, i.e. a number of clock ticks. These two ways of representing date ranges are semantically equivalent; each can handle all (and only) the business requirements that the other can. They differ only in details of implementation. Throughout these articles, we have chosen to represent date ranges with a pair of dates.
When a pair of dates is used, we must specify whether neither, either or both are included in the range they specify. Again, the options are semantically equivalent. In these articles, we have chosen to include begin dates in the range, but exclude end dates.
The timeline for policy P138, after this update, is shown below.
Figure 4: Timeline Diagram for Scenario 3.2
Note: graphics for timeline diagrams permit us to draw the lines indicating the start of a version only in alignment with the start of a vertical bar. Vertical bars represent months. So our convention will be to draw these version start lines at the start of the month of their ver-beg-dt. Thus, a ver-beg-dt of 3/14/05 is drawn aligned with the start of the vertical bar representing March, 2005.
After this change, we do have an end date for our initial version, although it is inferred rather than expressed as an explicit data value. However, we still do not have an end date for Mary's policy itself, as is graphically illustrated by the ellipsis on the above timeline.
On 6/12/06, Mary's policy is terminated. But in the Policy Version table, as shown in Scenario 3.1, we have no way to distinguish a version representing a policy deletion from one representing a policy insert.
To distinguish versions which logically delete a policy, we could add a delete flag. But a delete date carries more information than a delete flag. So while we do not need a second date to manage policy inserts and updates, we do need one to manage policy deletions.
Notice that the version date in the primary key is called version begin date, not version create date or version insert date. By the same token, we will call this second date object end date, not object delete date. And, of course, we do not call it version end date. It is the end date of the object, i.e., the policy, not of a version of the object.
After we terminate Mary's policy, the Policy Version table looks like this:
Figure 5: Policy Version Table After Termination of Policy
We can identify the first row as the initial version for P138 because there is no other row for P138 with an earlier ver-beg-dt. We can identify the second row as the terminal version, i.e. the logical delete point, for policy P138 because it is the only version for P138 whose obj-end-dt is not null.
The lifetime of policy P138 is from 1/12/04 to 6/11/06, inclusive. A query asking for what the policy looked like, at any given point during its lifetime, can now be satisfied.
Notice that these two rows are a physically condensed way of expressing the same semantics that would be expressed by having nearly 900 rows in the table, one for each day in the life of this policy. These two rows are semantically equivalent to a set of nearly 900 consecutive daily snapshots. This illustrates how, from a data storage perspective, versions are much more efficient than snapshots.
Notice also that this is the first instance in which we have physically updated a row in the Policy Version table. Prior to 6/12/06, the row with a 3/14/05 ver-beg-dt has a null obj-end-dt. From 6/12/06 forward, it does not. So we have overwritten that state of the row.
In doing so, have we lost information? In fact, we have not. We can infer from the second row above that its obj-end-dt would have been shown as null prior to 6/12/06, and as not null from that date forward. So although we did physically overwrite that row, we lost no information about the policy, or about what we knew about the policy at any point in its lifetime.
This shows why we think Dr. Snodgrass's metaphor of the relationship between nontemporal and temporal tables of the same "things" is a poor one. He encourages us to think of a temporal table as derived from a nontemporal one by adding various dates to it. So he would encourage us to think of the Policy Version table as the Policy table with time management added to it. Indeed, in his examples, he changes nothing in table names as he changes them from nontemporal to temporal. (This metaphor is first encountered in {Snodgrass 2000, p.20}, where a Lot table is still a Lot table after adding two pairs of dates. It is again encountered on p.113 (Adding History), p.117 "...adding valid-time support to a table..." and elsewhere.)
Throughout our discussion of Version Pattern 3, we have seen the profound difference between objects and versions of objects. In temporal tables, we physically manage rows which represent versions of objects. In the process, we logically/semantically manage those objects themselves. But in that process, the table whose rows represent those objects disappears! We have not kept a Policy table and added a Policy Version table. We have replaced the Policy table with a Policy Version table; and that version table should not be thought of as the Policy table with time management added to it.
The complete lifetime of policy P138 is shown on the timeline below.
Figure 6: Timeline Diagram for Scenario 3.3
It is only now, when the policy has been logically deleted, that we have full knowledge of its timeline. It is only now that the last version on the timeline is not followed by an ellipsis.
In Part 4, we defined four semantic constraints for Version Pattern 3. These constraints spell out the implications of the single-episode, no-gaps assumptions that are in effect for this version pattern. To repeat, those constraints are as follows:
In this installment, we have defined two dates which are needed to implement Version Pattern 3 - a version begin date and an object end date. Additional semantic constraints specify the rules for populating these dates.
The first group of additional constraints specifies the rules for populating these two dates during the insert of the first version for an object. Semantically, this is the action of inserting a new policy.
The second group of additional constraints specify the rules for populating these two dates during the insert of a noninitial, nonlogical delete version for an object. Semantically, this is the action of updating an existing policy.
However, for Version Pattern 3, this condition should never arise. Since a ver-beg-dt is also the system insert date, only an incorrect system clock could permit a version to have an earlier begin date than the version which preceded it. It shouldn't be necessary to write code to enforce {SC 3-7} because if the system clock is incorrect, we have a much bigger problem on our hands and will probably have to roll back and reapply updates anyway.
The third group of additional constraints specify the rules for populating object end date during a logical delete. Semantically, this is the action of terminating a policy.
Version Pattern 3 is a significant achievement. It retains the knowledge of the state of an object at any point during the lifetime of that object. Indeed, what more could there be? What more could businesses want that versioned tables can provide?
The answer is: quite a lot. To begin with, we might want to see what would happen with Version Pattern 3 if we distinguished business dates from system activity dates. This would mean that version begin date and object end date are business dates only. For system activity dates, we would then want to keep an insert date and a version update date. That latter date will be {null} except when the version is updated. And because with versions we are no longer considering updates in place, this update can only be one thing - the date the object end date was specified.
In the academic literature, versioning which has both a business set of dates and a system activity set of dates is said to implement a "bitemporal" pattern.
In providing an annotated list of version patterns, in Part 2, we did not distinguish a bitemporal and nonbitemporal variation of Version Pattern 3. (In addition, the description of Version Pattern 3 was incorrect, and should have stated the opposite of what it did state.) So following is a revised annotated list of the version patterns which we are discussing in these articles.
This list provides a correct description for pattern 3. It "bumps down" the other patterns, and specifies a pattern 4 which is pattern 3 with business and system activity dates distinguished. Finally, it reverses the sequence of the original list's ninth and tenth patterns in order to put the two patterns which describe snapshots next to one another.
Figure 7: Annotated List of Version Patterns - Revision 1
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.