With Pattern 3 and Pattern 4, each version of an object is distinguished from all other versions of the same object by means of a version begin date.
- The start of an object's lifetime is marked by the chronologically earliest version of that object.
- The end of an object's lifetime is marked by a chronologically latest version which contains an object end date.
- The current version of an object whose lifetime is not yet ended is marked by a chronologically latest version which contains {null} in its object end date.
Notice that these versions have no version end date. This is because Patterns 3 and 4 assume that every non-initial version follows its predecessor immediately, i.e., that when a new version is added, the previous version ends its business effectivity exactly one clock tick prior to the start of business effectivity of the newly added version. In other words, with Patterns 3 and 4, inserting a new version into a table determines both the begin date for that version, and also the (implicit) end date for what had been the current version until that insert took place.
A second assumption that characterizes Patterns 3 and 4 is that after an object has been deleted, it cannot reappear in the database. More technically, and using our Policy table example, given a version with a non-null object end date, no version with a later version begin date can have the same policy number. Equivalently, any version with a later begin date must be a version of a different policy. In Part 4, we introduced the concept of an episode, and characterized this second assumption as the assumption that no object can have more than one episode.
These two assumptions are semantic constraints on Patterns 3 and 4. If your business requirements for versioned history rule out either or both of these assumptions, then neither of these patterns will meet your needs.
Patterns 3 and 4
A version pattern is most precisely defined by the combination of a) its business requirement (noted as {BR-x}, where x is any number) which the pattern must satisfy, and b) its semantic constraints (noted as {SC-x-y}, where x is the number for the pattern, and y is the number for the constraint). In these terms, Pattern 4, which is the pattern we will examine in this article, satisfies the same business requirement and operates under the same semantic constraints as Pattern 3 - with one exception. That exception is semantic constraint {SC 3-5}:
- {SC 3-5}. Ver-beg-dt cannot be in the past nor in the future because it is also the system insert date. To set such a date to anything but [now] would be a lie. We can't do anything in the past, nor in the future. [Now] is all we have.
With Pattern 4, this constraint is changed. Semantic constraints 1 - 4 and 6 - 7 are identical to their corresponding Pattern 3 constraints. But Pattern 4 has no constraint corresponding to {SC 3-5} because it uses distinct dates to indicate when the version becomes effective (ver-beg-dt) and when the version is inserted into its table (crt-dt).
Bi-Temporality
With Pattern 4, we introduce a second set of dates. The first set is business effectivity dates. For Patterns 3 and 4, they are:
- Version-begin-date (ver-beg-dt): the clock tick on which that version of the object becomes the currently in effect version.
- Object-end-date (obj-end-dt): the last clock tick on which the last version of the object is in effect.
The second set, included in Pattern 4 but not Pattern 3, also consists of a pair of dates. They are:
- Create-date (crt-dt): the clock tick on which that row is physically inserted into its table.
- Last update date (last-updt-dt): the clock tick on which that row is physically updated.
Normally, versions aren't updated. And indeed, versions are never logically updated. The information they contain is never lost. But as noted in Part 5, there is one case in which a version may be physically updated. That happens when a transaction is received which directs the system to logically delete the object. This logical delete of the object is implemented by physically updating the current version, changing its object end date from {null} to the date of the logical delete. When this physical update takes place, the last update date records the clock tick on which it does. In all other cases, last update date is null.
These two sets of dates are orthogonal to one another. Changes to one set do not necessarily entail changes to the other set.
Computer scientists have called the use of these two sets of dates the "bi-temporal" data pattern. The difference between Patterns 3 and 4, then, is solely that Pattern 3 is not bi-temporal while Pattern 4 is.
To work out the implications of this difference between the two patterns, let's begin by recreating the series of events described in Scenario 1.
Scenario 4.1: a Proactive Insert
With Pattern 4, it is now possible to physically insert a row on a different date than the date it becomes effective. So let's assume that Mary's policy, entered into the database on 1/12/04, does not become effective until 6/1/04. (Correction: In scenario templates, the fourth cell down on the left-hand side specifies the date or dates on which the version could validly have been inserted. In Part 5, Scenario 3.1, that cell contains "1/12/04 or any later date." It should contain "N/A," because for that Version Pattern, there is no choice about when the insert takes place; it must take place on the ver-beg-dt.)
