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.
Be the first to comment on this post using the section below.