Note: a glossary of technical terms used in these articles can be found on the Web sites of the authors at and In this column, we discuss a bi-temporal  transaction which results in the merger of two episodes. Figure 1 shows two episodes, four months apart. In this example, it is now March 1, 2009. An original insert transaction has been submitted, one intended to create a version of policy P861 that is effective from 4/1/08 to 8/1/08. For eleven months, we have overlooked the fact that P861 was in effect during those four months of last year. We are now going to correct our oversight.

We already know that an original insert does not update an existing episode; it creates a new one. We also know that, for the same object, every episode is separated from adjacent episodes by at least one clock tick. And for this transaction, that is a problem. Perhaps the person submitting the transaction does not know there already is an episode for P861 that is effective up to 4/01/08. That person may not know that there already is an episode for P861 that begins on 8/01/08. But either episode is sufficient to render this original transaction invalid. If it were applied to the database, the resulting version would not create a new episode, because it would be clock-tick contiguous with both the episodes already in the table. But it reasonable to require the user to know about these episodes, and to require him/her to write an original update transaction instead of an insert? Why can't our asserted versioning framework code, having determined that the transaction does not specify any effective time for P861 that is already specified in the target table, simply apply the transaction?  Asserted versioning framework code certainly could do this, and the result would be a valid state of the database – one continuous episode from January 2008 through to the present. But just as an insert against a conventional table expresses the user's assumption that a matching row does not already exist in that table, so too an insert against an asserted version table expresses the user's assumption that his/her transaction will create a new episode, not modify an existing one. And in this case, that assumption is wrong.  Some database management systems support what is called an "upsert" transaction. This is a transaction that will be treated as an insert if a matching row does not already exist in the target table but will be treated as an update if a matching row does exist. We could define an asserted versioning upsert transaction and write the framework code to handle it. If we did so and the transaction in Figure 1 was changed to an upsert, then it would be a valid transaction and we could apply it to its target table. But lacking an asserted versioning upsert (for now), we must require the user to submit an original update instead of an original insert. This new transaction is shown in Figure 2.

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