Related Links

Web Seminars

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.

Tables and Keys

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.

Business Dates and System Dates

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.

Scenario 3.1: A Policy Insert in the Policy Version Table

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.

Scenario 3.2: A Policy Update 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.