Databases Reference
In-Depth Information
BK
P861
P861
ver-dt
client
type
copay
$15
$20
ver-end
crt-dt
Jan10
Apr10
updt-dt
Mar10
C882
C882
HMO
May10
Apr10
May10
HMO
Aug10
Jul10
Figure 4.10 Effective Time Versioning: After Three Proactive Transactions.
appear in the current view exactly when it is due to go into
effect, and not a moment before or a moment after. The time
at which physical maintenance is done is then completely inde-
pendent of the time at which its results become eligible for
retrieval as current data.
Proactive updates or deletes are just as straightforward. For
example, suppose we had processed a proactive update and then
a proactive delete in, respectively, April and July. In that case, our
Policy table would be as shown in
Figure 4.10
.
To see how three transactions resulted in these two versions,
let's read the history of P861 as recorded here. In January, we
created a version of P861 which would not take effect until
March. Not knowing the version end date, at the time of the
transaction, that column was given a value of 12/31/9999. In
April, we created a second version which would not take effect
until May. In order to avoid any gap in coverage, we also updated
the version end date of the previous version to May. Not knowing
the version end date of this new version, we gave it a value of
12/31/9999.
Finally, in July, we were told by the business that the policy
would terminate in August. Only then did we know the end date
for the current version of the policy. Therefore, in July, we
updated the version end date on the then-current version of
the policy, changing its value from 12/31/9999 to August.
Effective Time Versioning and Retroactive Updates
We might ask what kind of an update was applied to the first
row in April, and to the second row in July. This is a version table,
and so aren't updates supposed to result in new versions added
to the table? But as we can see, no new versions were created
on either of those dates. So those two updates must have
overwritten data on the two versions that are in the table.
There are a couple of reasons for overwriting data on vers-
ions. One is that there is a business rule that some columns
should be updated in place whereas other columns should be
versioned. In our Policy table, we can see that copay amount is
one of those columns that will cause a new version to be created
