Note: a glossary of technical terms used in these articles can be found on the Web sites of the authors. In addition, a listing of earlier articles and/or columns in this series can also be found on, and

This column discusses proactive updates, updates in which the effective begin date of the version carrying the update lies in the future.

In Scenario 1, we described updates that were forward extensions of episodes. Proactive updates are also forward extensions. The difference lies in default values. In Scenario 1, we accepted the original transaction default values for the effective begin and end dates and the assertion begin date. For all three the default value is the date current when the transaction is physically carried out. In a proactive update, we override the default for the effective begin and date, instead, specify a future date on the original transaction.

So, our taxonomy diagram remains at four transformations completed with four to go, as shown in Figure 1.

Figure 1: Asserted Versioning State Transformations - Four Complete, Four to Go

A proactive update adds a version to an episode with an effective begin date in the future. For such a transaction to be valid, however, the update cannot introduce a gap in effectivity time into the episode. If it did, it would not be updating an episode; it would be creating a new episode. Allowing that to happen would be equivalent to allowing an update against a non-temporal table to be turned into an insert if the target row of the update isn't already in the table. Some database management systems support what is called an upsert transaction, which applies its data as an insert if its target row is not in the table, and as an update if it is.

So we adopt the following rule for asserted version original updates: They are valid only if a currently asserted episode exists with an effective time that is either adjacent to the effective begin date on the transaction or has an effective time that includes that date. We will have more to say about this point later on, when we discuss temporal integrity constraints. For now, as we move through our taxonomy, we will only discuss valid examples of each of the nodes in the taxonomy, leaving the discussion of temporal integrity constraints until later.

Proactive updates can be applied to either open-ended or close-ended episodes. Close-ended episodes have a known effective end date; open-ended episodes do not. Open-ended episodes are fairly common. Whenever we create or update an episode without knowing when the next change to that episode will occur, or when the episode will end, the result is an open-ended episode, one whose episode effective end date (which is the same as the end date of its latest version) has the value 9999.

In this installment of the series, we will discuss a proactive update to an open-ended episode.

A Proactive Update to an Open-Ended Episode

Figure 2 shows an episode, that began on 8/01/04. It is now 3/1/05, and the original transaction specifies that policy P861 will be assigned policy type PPO in exactly three months, on 6/1/05.

Figure 2: Extending Forwards - Before Image

The asserted versioning (AV) Policy table shows one row and, therefore, an episode with one version. The timeline diagram shows the same thing. The table states that the episode began on 8/01/04, and is both currently in effect (on 3/01/05) and will remain in effect "until further notice" (until 12/31/9999). This version was asserted on 8/01/04, and the assertion is in effect now and will remain in effect until further notice. The timeline diagram contains two arrows, both of which begin on 8/01/04, and both of which are current now, and both of which are open-ended (i.e., right-pointing arrows rather than line segments).

Because a proactive update is an update, it is translated into three temporal transactions, just as any original update is. That translation is shown in Figure 3.

Figure 3: Translating an Original Update

All three temporal transactions are asserted on the same date they are physically applied. The only exception to this rule are deferred assertions, whose consideration we have postponed until after all variations on the theme of non-deferred assertions have been exhausted. So the withdrawal of the current assertion - row 1 in Figure 4 - is carried out on 3/01/05, as is shown in that row's assertion end date. And the assertion of row 1's replacement, and row 1's successor (rows 2 and 3, respectively), is carried out on 3/01/05, as is shown in the assertion begin dates of those rows.

Figure 4: Extending Forwards - After Image

Because this is an update, and the successor version begins on 6/01/05, the version it succeeds must end on that same clock tick. Since this episode is an open-ended episode, one with no specified effective end date, this means that the update creates a replacement version whose effective end date matches the effective begin date of the successor version. In other words, it interprets the intent of the original transaction to be to place a new most current version in the episode, and to adjust its immediate predecessor so there is neither an effective time overlap nor an effective time gap between them. As we indicated above, it will not make this adjustment if the episode ends prior to the new effective begin date; it has no adjustment to make if the episode already ends on that date; and, finally, it will make the adjustment - by truncating the episode's latest version back to that begin date - otherwise.

It should be clear that this interpretation of a proactive update is neither arbitrary nor contrary to the intentions of its authors. Specifically:

In the case of a gap between the episode end date and the update transaction's begin date, the transaction is rejected. This is equivalent to an update against a non-temporal table being rejected when its target row doesn't exist.

In the case of the episode end date and the transaction's begin date being identical, the update transaction is applied. This is equivalent to an update against a non-temporal table being applied when its target row does exist.

In the last case, for which there is no parallel with non-temporal tables, accepting the update transaction amounts to overriding what the episode currently says about what the object is going to be like starting on the effective date specified in the transaction. In other words, it treats the update as a correction to an anticipated future state of the object in question.

After the update is applied, the database is as shown in Figure 4. In assertion time, the episode consisted of row 1 and row 1 only, from 8/01/04 to 3/01/05. In the assertion time that begins on 3/01/05, row 1 is no longer part of the episode; instead the episode consists of rows 2 and 3. An open-ended current episode before the update remains an open-ended current episode after the update. An episode with n versions in pre-transaction assertion time now has n+1 versions in post-transaction assertion time.

Because we are overriding a default date, the timeline diagram is a little more difficult to draw. We eschewed a two-dimensional diagram because the ones we have seen just didn't seem intuitive to us. But now we have the following diagrammatic problem.

Row 1 is represent by the arrow/line pair in the shaded area. That shaded area is where the graphics for no longer asserted versions are placed. Row 2 is shown by the red arrow, placed at 3/01/05. Because this is not a deferred assertion, it begins on the transaction date. The assertion concerns the first version of this episode, as the graphic shows. It states that the effective time covered by that version extends from 8/01/04 to 6/01/05.

So far, so good. But it is the graphical representation of row 3 that causes the trouble. Row 3 is shown beginning on 6/01/05, as indicated by the green arrow. It is the second version of the episode, and the vertical bar separates it from the first version, in effective time. But it is now 3/01/05, not 6/01/05; and since our transaction is not deferred, row 3 also begins to be asserted at the time of the transaction, 3/01/05.

How do we show that? Not only does this situation require an assertion time indicator for a version to be placed outside the effective time box for that version; it also has to show that both versions (rows 2 and 3) are identical in assertion time.

Our solution, as imperfect as it is, works like this:

  • Row 1. The green arrow and red bar in the shaded area represent the version which was superceded by the update. This version is preserved because it has the original effective end date of the first version of the episode, information that would otherwise be lost. The graphics for this row lie in the shaded area to indicate that it is no longer a currently asserted version. We no longer claim that it is correct.
  • Row 2. The green line and red arrow below the shaded area represent the version which superceded the row 1 version. This version is identical to that represented by row 1, except for the effective and assertion end dates. The non-9999 effective end date turns a green arrow into a green line segment.
  • Row 3. Like row 2, this row shows a version with an assertion begin date of 3/01/05. But this date is earlier than the effective begin date of that version, and so, graphically, lies to the left of the box representing that version. Our solution has been to place a dotted line linking the second version to the assertion arrow of its predecessor version.

Proactive updates modify an episode by extending it forwards. If the version carrying the update begins when the transaction is applied, it is a normal update. But if the version carrying the update begins at a later point in time, it is a proactive update. Any update to an episode - retroactive, current or proactive - must insure the integrity of the episode by guaranteeing that the episode remains a set of effective time sequenced versions which have neither gaps nor overlaps between adjacent pairs.
Note as well the focus on episodes. Physically, any original update terminates one row of an AV table, inserts a replacement row, and then also inserts a successor row. But the semantic unit of work is what the user intends to do to the object he is updating. That is translated into a modification to the episode of that object which is adjacent to or wholly or partially inclusive of the effective period specified on the transaction. The person writing an original transaction thinks in terms of objects. The software implementing asserted versioning thinks in terms of episodes, and issues physical transactions to the database management system which affect rows.

Next time, we will discuss proactive updates to closed episodes, ones which have a known end date.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access