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 glossary has been recently updated.

In Part 1, we began discussing proactive updates, which are updates that have an effective begin date in the future. We explained how to carry out a proactive update to an open episode, one that has a 12/31/9999 effective end date. Such episodes are open in the sense that they will continue on in effective time until something happens to them.

A closed episode is one with a non-12/31/9999 effective end date. In other words, it is an episode that will end on a particular date unless something happens to it between now and then.

Before proceeding to our example of a proactive update to a closed episode, lets stop and take stock of where we are. We take this opportunity to will clarify some of the basic concepts that we have been using and, in some cases, re-word their canonical descriptions.

Where We Are

In the "Time and Time Again: Scenario 1" installment, and continuing through Transaction 2, 3, and 4, we developed Scenario 1. This is the "plain vanilla" scenario for asserted versioning. It consists of an original insert, two original updates and an original delete. In our experience, 75 percent or more of the transactions against asserted version tables are covered by Scenario 1. This is because the Scenario 1 transactions accept default values for all three bi-temporal dates that may be specified on original transactions. These dates are, in the positional sequence in which they appear on those transactions - the effective begin date, effective end date and the assertion begin date. The default value for the begin dates is the date the transaction is applied. The default value for the effective end date is 12/31/9999.

These default values represent the most typical transaction situation. This is the situation, when a bi-temporal transaction is applied. The intent is for it to take effect immediately, to remain in effect until further notice and to be asserted immediately.

In the "Time and Time Again: State Transformations" installment, we present a taxonomy of asserted version state transformations. These are transformations that create or terminate an episode of an asserted version managed object, or that alter the effective time period of one or more such episodes by extending the effective time of a single episode backward or forward by merging two episodes into one or by splitting one episode into two.

Scenario 1 covered the state transformations that created an episode, terminated an episode and extended an episode forward in effective time. Then, in the "Time and Time Again: Retroactive Updates," we discussed the state transformation in which an episode was extended backwards in time. This takes us up to our last column, in which we returned to state transformations in which an episode is extended forward in effective time. But instead of accepting the default value of {now} for the effective begin date on the transaction, we began to consider original update transactions that specified a future effective begin date. We are calling these transactions "proactive," because they physically update asserted version tables prior to the time at which those updates take effect (i.e., prior to their effective begin dates).

The first of such transactions updated an open episode, one whose effective end date is 12/31/9999. Because that is the default value on a transaction for that date, that proactive transaction did not specify an effective end date. This time we intend to discuss proactive updates that extend an episode forward in effective time, but have targets that are closed episodes. The example we will use begins with a closed episode with an effective time running from 1/01/08 to 1/01/09. The transaction will update it so that its effective time runs from 1/01/08 to 3/01/09.

Note that proactive updates do not necessarily extend an episode forward. For example, if we did not include an effective end date on the transaction that updates a closed episode, that end date would default to the end date of the episode being updated. In that case, the episode's effective time period would not have been altered. In the next example we will present, that effective end date would have been 1/01/09. But instead of accepting the default effective end date, in this example, we will specify an end date that extended the episode forward in effective time, from 1/01/09 to 3/01/09.

Open Episodes and 12/31/9999

Open episodes are far more common that closed episodes. This is because open episodes correspond to the typical situation with respect to a conventional table. Typically, when a new row is inserted, or when an existing row is updated, we don't know how long it will be until something else happens to that row, until it is updated or deleted. When we don't know when an episode will end or when it will be updated, our assumption is that it will not end and will continue on in its present state unless and until something is done to it. This is what the value "12/31/9999" means in the effective end date column of an episode's latest version.

So by convention, we stipulate that an end date of 12/31/9999 does not represent a particular New Year's Eve, nearly 8,000 years from now. And by letting the database management system interpret the value "12/31/9999" as a date, we guarantee that any query will find the effective end date of the episode later than {now}, whenever that happens to be. In short, whenever we come across an open episode, we always assume that it is - at that moment - in effect.

Updating Closed Episodes

But sometimes we do know how long it will be until data describing the current state of an object will be changed or deleted. For example, we may know on May 1 that a policy holder has requested that, effective August 1, we extend her policy by three months, change her policy type and reduce coverage in certain areas, thus lowering her co-pay amount by $5.

With a conventional table, we must put this change transaction into a batch transaction file that will be run in the batch window between July 31 and August 1. If we apply the transaction earlier or any later, we will introduce erroneous data into the database.

In situations like this one, a company's response often depends on whether or not it is confident that it can always provide "just in time" processing of policy change transactions. Whether it can or not depends on several things. For one thing, there must be a batch window between every day on which a policy change can take effect, and the day before that. Every batch window must be large enough that the business is confident that the volume of change transactions on any given day will never exceed the offline time available to complete all of them.

But if our policy data resides in an asserted version table, in any bi-temporal table or even in a plain vanilla version table (the kind created by adding a date as the low-order part of the primary key of an otherwise conventional table), we don't have to rely on "just in time" processing and "large enough" batch windows. That said, we turn to an example of a proactive update being applied to a closed episode of a policy in an asserted version table, both updating that policy and extending it three months into the future.

We begin with Figure 1, which shows a closed episode of policy P861 and an original update transaction that we wish to apply three months before it takes effect.

Figure 1: A Closed Episode and a Proactive Update - Before Image

Please note, in Figures 1 and 2:

  • The large black arrow points to the current date, in this case to 5/1/08. This is an addition to the Figures we have been using up till now.
  • Open episodes have no vertical line on their right-hand side, closed episodes do. Whether the episode was closed sometime in the past or is going to become closed sometime in the future, a vertical line at the right-side of the episode's latest version marks the date the episode ended or will end (unless, if that date is in the future, something happens to the episode in the meantime).

As previously stated, we adopt this 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 an effective time that includes that date. In this case, the effective time period for the only version of this episode is 1/01/08 to 1/01/09, and this time period does include the effective begin date of 8/01/08. So this proactive update is valid, and we may proceed to apply it to the database.
This update of a closed episode is a proactive update because the transaction will be applied to the database three months before the transaction's data becomes effective. Like any original update to an asserted version table, three physical database actions are required. They are shown in Figure 2.

Figure 2: Mapping an Original Update - Revised Version

Figure 2 differs in several important respects from the Figure 2 in Part 1 of this column. We will discuss those differences next time, as we continue our discussions prior to completing an original proactive update to a closed episode.

Johnston and Weis will be publishing a book on temporal data management, based in large part on the material in this series. It will be published by Morgan-Kaufmann and available in the second quarter of 2010.