Version Pattern 1: Updating in Place
However, even with tables that lack queryable history, it is quite common for developers to include both a create date and a last update date; these two dates provide a minimal level of support for history. Let's include them as business requirements for Version Pattern 1 and note them as follows:
{BR 1}. Retain the date each row was created, and also the date of its last update.
The scenarios below illustrate Version Pattern 1. Our example will be an insurance policy table.
Scenario 1.1: an Insert to the Policy Table
On 1/12/04, a policy for client Mary Jaspers is inserted into the Policy table for the Acme Healthcare Insurance Company (AHIC). For now, we will assume that this is also the action that makes the policy effective. In other words, a policy with AHIC becomes effective on the date that a row representing that policy is inserted into AHIC's Policy table.
Note: although 1/12/04 is in the past, as of the date of publication of this article, the paragraph above said that on "1/12/04, a policy for client Mary Jaspers is inserted into the Policy table" (italics added). It did not say ".... A policy ...... was inserted .......". This "is" should be consider a tenseless "is," not an indication of the present tense. The reason to speak tenselessly whenever possible is that otherwise, the tenses involved in an English language description of these situations simply become too complex. With a tenseless natural language description, we will not be distracted from the focus of these articles, which is the management of time in relational databases, not the management of time and tenses in natural languages.
In later history patterns, we will drop this assumption, and distinguish system activity dates such as dates rows were inserted, updated or deleted, from business effectivity dates such as begin and end dates of business effectivity, or the effectivity dates for a correction to an earlier entry. This distinction between system activity and business effectivity dates is referred to, in the literature, as the "bi-temporal" distinction.
Note: those in the health care insurance industry will recognize that this example about policies is not a typical situation. Typically, health care insurance policies are group policies, which are contracts between a client company and the insurance company, not a contract between the insured individuals and the insurance company.
In the property and casualty industry, on the other hand, policies which cover an individual or family are usually contracts between the insurance company and that individual or family. But health care insurance companies do issue individual policies, and we believe that this will provide a simpler set of examples with which to illustrate our proposal for managing versioned history.
Scenario 1.1 shows the result of entering Mary's policy into the Policy table. (See the previous article - Part 2 - for an explanation of the scenario template used here and throughout the remaining articles in this series.)
.gif)
Note: the conventions for distinguishing different types of columns in these illustrative tables are as follows:
- Primary key columns are listed left-most, and the column headings are bold-faced. They contain the suffix (PK).
- Foreign key columns are listed next, and the column headings are underlined. They contain the suffix (FK).
Note: these articles do not contain examples of foreign keys which are also primary keys. One of us (Johnston) has written several articles explaining why he thinks that foreign keys should never occur in primary keys. See References for links to these articles.
- Non-primary key and non-foreign key business data columns are listed next.
- Metadata columns, such as crt-dt and last-updt-dt, are listed last.
The client-nbr foreign key can be ignored for now. It will be used in later scenarios. The same is true for [now], whose template cell contains "n/a" (for "not applicable"). [Now] (the brackets indicate that we are talking about a metadata item, and not merely referring to the present moment) will not become relevant until we move on to more complex scenarios.
The policy type is PPO (preferred provider option). The first metadata column (crt-dt) tells us that the row was inserted on 1/12/04. The {null} in the second metadata column (last-updt-dt) tells us that, so far, this row has not been updated.
Presumably there are many other rows and columns in the Policy table. But we won't show any of them until later on, when they are needed to illustrate discussions of more complex patterns.
Scenario 1.2: an Update to the Policy 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 table, Mary's Policy row looks like this:
.gif)
The use of a create date and last update date is common in business IT. And with those two dates, we are not entirely lacking in temporal metadata about this row. Crt-dt tells us that the row was inserted on 1/12/04, last-updt-dt that it was last changed on 3/14/05.
