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 DMReview.com, MindfulData.com and InbaseInc.com.

 

Last month, we issued an insert transaction against an asserted version table on 1/01/04. For discussion purposes, it is now 5/01/04, and in the intervening four months, nothing has happened to the policy table. Today we are going to update the co-pay amount on the policy from $15 to $20. The state of the database prior to this update is shown in Figure 1 (see PDF below).

 

Please note that these diagrams have changed from last month’s column:

 

  • Columns that cannot be updated (and also the row number, which is not a column) have column headings shaded in gray.
  • Because we are using full dates for our clock ticks in each example, we have changed the label “Time” to “Date.”
  • We have included an actual date in the “txn” column.
  • In the box containing the original transaction, we have comma-delimited columns that can be modified (i.e., those that are not shaded in gray).

The row for Figure 1 can be read as follows:

 

  • It represents policy P861 (oid).
  • The business data on this row became effective on 1/01/04 (eff-beg).
  • It is currently in effect and will remain in effect until further notice (eff-end).
  • Since 1/01/04, this policy has been owned by client C882 (client) and is an HMO policy (type) with a $15 (co-pay).
  • This row was inserted into the table on 1/01/04 (txn).
  • It was immediately asserted based on what, to the best of our knowledge, is a true statement (asr-beg).
  • Until further notice, it will be the information we return to any query asking about this policy during this current effective time period which began on 1/01/04 (asr-end).
  • At the time this row was created, no row for P861 was current (i.e., it was not the case that there was a row for P861 for which effective time period and assertion time period were both then current).

Updating Policy P861

 

Updating policy P861 is not the same thing as updating the row we currently have for that policy like it would if the policy table were a non-temporal table. Instead, to update the policy object while retaining the data that is about to be superseded by the new data in the update transaction, three physical actions have to be performed on the target table. Figure 2 (see PDF below) shows the original update transaction and its translation into three temporal transactions. We will explain each of the temporal transactions as we go along.

 

The result of applying the first of these three temporal transactions is shown in Figure 3 (see PDF below). This temporal transaction withdraws the current assertion. It does so by doing a physical update of row 1 - overwriting its assertion end date with the date the new transaction will begin to be asserted. In this case, that is the same date as the date of the update itself, 5/01/04.

 

In Figure 3, the database now shows that row 1 was asserted from 1/01/04 to 5/01/04 (inclusive of the earlier date and exclusive of the later date, as explained in our earlier discussion of the closed-open method of representing time periods with dates). The change to the row has been indicated in boldface. The assertion end date, which was 12/31/9999 prior to this transaction, has been changed to 5/01/04.

 

We know that the original update transaction will end the current version's effective period on the indicated date, and then it will add a new current version whose effective period begins on that same date. In other words, we know that the update transaction will replace the current version with a new current version, and will do so while preserving temporal contiguity with the version it supersedes. How do we know this? We know it because that is what an update against a bi-temporal table means. An update against a non-temporal table doesn't introduce any temporal gaps; there is no way it could do that. Thus, to preserve those same semantics, an update against a bi-temporal table must not introduce any temporal gaps, even though it could.

 

With a non-temporal table, if we want to record a temporal gap - a period of time in which policy P861 existed, followed by a period of time in which it did not and a period of time in which it existed again - we know how to do this. We do it by deleting the policy, thus inaugurating its non-existence phase and, later on, inserting it again, ending its non-existence phase.

 

So to preserve the semantics of what inserts, updates and deletes mean, we must ensure that a temporal gap for the object being managed can be created only by an original delete, followed later on by an original insert of the same object. Because the original transaction being considered is an update and neither a delete nor an insert, we must carry it out so that the presence of the object in the target table continues, without a break.

 

For this reason, the first step in carrying out an original update against an asserted version table must be to preserve the then-current version by moving it into past assertion time. We do this with a physical update, one that overwrites the current version's assertion end date with the assertion begin date of the new version, which will become the new current version. That date happens to be the physical transaction date. Figure 3 shows the results of applying the first temporal transaction for this original update.

 

In Figure 3, row 1 is shaded. This is a graphical convention we will use to indicate rows that are no longer currently asserted, no longer claimed to be correct. This row says that from 1/01/04 to 12/31/9999, policy P861 will be as shown. But based on the information supplied by the original update transaction, we now know that it is not true that the data in row 1 is the truth about the policy from 1/01/04 to 12/31/9999. We now know that, starting on 5/01/04, the data in row 1 ceases to be a correct description of the object.

 

Reading the Timeline Diagram

 

Now is a good time to begin explaining the red and green arrow/lines above the timeline in these figures. In Figure 1, there is one red and one green arrow. Both begin on 1/01/04 and extend up to the current point in time, which 5/01/04 is in this case. Both are arrows, not lines; and so, as the legend in Figure 3 explains, this means that their respective timelines have no known end point, a situation represented by our use of 12/31/9999 as the end date.

 

Note the change in these timelines from Figure 1 to Figure 3. In Figure 1, the assertion time period is open, as we shall say. The graphic is an arrow, not a line; and corresponding to it, the end date is 12/31/9999. But in Figure 3, the assertion time period is closed. The graphic is a line, not an arrow; corresponding to it, the end date is not 12/31/9999.

 

In these diagrams, we reserve the unshaded area directly above the blue rectangles for timeline arrows or lines of versions that have no known assertion end date (i.e., whose end date value is 12/31/9999). As soon as that end date changes, we will move the timeline arrows for that row up to the shaded area. This will make room for the timeline arrows for the row that replaces the no-longer asserted row. Our next column will show what this looks like.

 

A Bi-Temporal Update in Place

 

In general, updates in bi-temporal tables are carried out with physical insert transactions. And as we will show next time, this original update will be completed by means of two physical inserts. However, something needed to be done to row 1 to move it into past assertion time. Until this update transaction began, row 1 had an assertion end date of 12/31/9999, and it now has an assertion end date of 5/01/04, indicating that we will no longer claim that this row is correct from this date forward. It is now incorrect because its effective end date is wrong.

 

Updates in place overwrite the data they update. However, we have not lost the information that this row originally had an assertion end date of 12/31/9999. The reason is that no row can be physically added to an asserted version table with any assertion end date other than 12/31/9999. The internal mechanisms which translate original into temporal transactions will guarantee that. Therefore, the assertion end date in row 1, as it exists in Figure 3, tells us two things. It tells us that from 1/01/04 (the assertion begin date) up to (but not including) 5/01/04 this row had an assertion end date of 12/31/9999. It also tells us that, starting on 5/01/04, it will no longer be asserted. Any asserted version with a non-12/31/9999 assertion end date, is one that will be (or was) moved into past assertion time one clock tick prior to that non-12/31/9999 date.

 

Our original update transaction has begun, but there are further modifications to the database that must occur before the transaction is complete. This first physical update must be part of an atomic transaction. If the next temporal transaction, which is a physical insert, fails, then this physical update to row 1 must be undone. If the second physical insert fails, then both prior physical transactions must be undone. Fortunately, thanks to Jim Gray above all, we know how to do this.

 

Next time, we will complete this original update against our asserted version policy table. After that, we will apply an original delete transaction to our database. But our readers should not assume that, with an original insert, update and delete transaction completed, there is nothing else of importance to say about asserted versioning in particular, or bi-temporal data in general. On the contrary, at that point we will have just scratched the surface of the complex but powerful semantics.

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