Time and Time Again: Managing Time in Relational Databases, Part 2 - Preliminaries

InfoManagement Direct, May 2007

Tom Johnston, Randall Weis

The needs which businesses have for versioned history are many and varied. However, those needs and the data patterns which satisfy them seem to fall quite nicely along a linear progression from simple to complex. We will call the patterns that satisfy each set of business needs "version patterns" because, in the terms organized by the taxonomy in Part 1 of this series, we are concerned with queryable history of the states of persistent objects, kept as versions of those objects.

However, our concern will not be exclusively with past and present versions. The more general topic is maintaining and querying tables which contain noncurrent as well as current versions of their persistent objects. And there are other kinds of noncurrent versions than historical ones. We will also consider versions for error correction, versions for future states, versions which overlap in time, coordinated (pending/sandbox) updates and even versions for hypothetical states.

Version Patterns: Manner of Presentation

In this series of articles, we will discuss a dozen version patterns. Each pattern will be introduced by describing the business requirements it supports.

Next, we will show how these version patterns work by presenting several scenarios and timelines for each pattern. Each scenario will show the results of doing an insert, update or delete against a table constructed according to the pattern. Each timeline will show what would be returned by a query against the past, present or future state of the object whose versions are being used as the example.

Initially, these transactions will be simple ones that physically affect just one row in one table. But gradually, more semantically expressive scenarios will be considered, ones in which what is seen as one transaction to the issuing code or person will result in several rows being affected in possibly several tables.

Following the scenarios for each history pattern, we will present and comment on a list of semantic constraints that must be followed for the pattern to work. Most of these constraints can be enforced only by triggers, stored procedures or application code. It is an indication of how "special" and complex temporal state management is that the normal DBMS entity and referential integrity mechanisms can do very little to enforce the semantics of these patterns.

Finally, each pattern will conclude with a list of objections to it that we have encountered. Some of these objections are valid in the sense that if the different business requirements for the pattern were weighted a little differently, a different pattern or at least a different physical implementation than that suggested by the pattern would be appropriate. Other objections are not valid, either because the pattern was not fully understood by those criticizing it, or because the flaws in the alternative pattern that the critics preferred were not fully understood.

In this series of articles, it will not be possible to discuss all the objections to these patterns that we are aware of. But we can, and will, at least note the existence of the objections we are aware of.

Following is an annotated list of the version patterns which we will discuss in these articles.

Figure 1: Annotated List of Version Patterns

Figure 2 shows the template we will use to present scenarios.

Figure 2: The Scenario Template

The numbered boxes in the template will contain the following information:

1. Name of the scenario.
2. Table used for this scenario.
3. Definition of the table.
4. Description of the scenario.
5. Now (whatever date that may be).
6. Column headings for the table.
7. One or more rows of the table.

Basically, boxes 1 through 5 contain metadata that is necessary to understand the scenario. Boxes 7 are the column headings for the table under discussion. There are one or more rows indicated as boxes 8, and each one represents a row in a relational table.

Timelines

Except for the first two patterns, we will also use a timeline diagram to explain what is going on. Figure 3 illustrates this diagram. It will be used beginning with Version Pattern 3, which is the first pattern that goes beyond a simple update in place approach to history.

Figure 3: The Timeline Diagram

Health Care Insurance: A Business Example for Version Patterns

Health care insurance is not the only industry which has a need for real-time history. But the insurance industry in general is certainly one in which real-time history is very important. Two paradigmatic insurance situations make the point: processing a claim and answering a customer telephone inquiry.

As for the first paradigmatic situation, a claim is filed for an event which happened at some point in the past. The claimant has a policy with the insurance company, to which she sends the claim. But the policy which is relevant to the claim is not her policy as it is when the claim is received. Rather, it is her policy as it was when the claimable event occurred.

In property insurance, the claimable event is damage to property. In health care insurance, the claimable event is a service event, an event in which a doctor, hospital or other health care professional or organization provided medical services to the claimant.

Processing a claim involves comparing the claim to a past state of the policy against which the claim is being made. That past state may happen to be identical to the current state of the policy. But the point is that it equally well may not. And, in point of fact, it often is different. So processing a claim requires real-time access to historical data.

Page 1 of 3.