Databases Reference
In-Depth Information
BK
P861
ver-dt
client
type
copay
ver-end
crt-dt
May10
updt-dt
Mar10
C882
HMO
$15
9999
{null}
Figure 4.11 Effective Time Versioning: A Retroactive Insert Transaction.
effect in March. What should we do? Well, by analogy with a pro-
active insert, we might do a retroactive insert, as shown in
Figure 4.11
.
So suppose that it is now June, and we are asked to run a
report on all policies that were in effect on April 10
th
. The
WHERE clause of the query underlying that report would be
something like this:
WHERE ver_dt <¼ '04/10/2010' AND '04/10/2010' < ver_end
Based on a query using this filter, run on June 1
st
,thereport
would include the version shown. But suppose now that we had
already run the very same report, and that we did so back on April
25
th
, and the business intent is to rerun that report, getting exactly
the same results. So it uses the same query, with the same WHERE
clause. Clearly, however, the report run back on April 25
th
did not
include P861, which didn't make its way into the table until May 1
st
.
If there is any chance that retroactive inserts may have been
applied to a version table, the WHERE clause predicate we have
been using is inadequate, because it only allows us to pick out a
“when in effect” point in time. We also need to pick out a “when
in the life of the data in the table” point in time. And for that pur-
pose, we can use the create date.
With this new WHERE clause, we can do this. The filter
WHERE ver_dt <¼ '04/10/2010' AND '04/01/2010' < ver_end
AND crt_dt <¼ '04/25/2010'
will return all versions in effect on 4/10/2010, provided those
physical rows were in the table no later than 4/25/2010. And
the filter
WHERE ver_dt <¼ '04/10/2010' AND '04/10/2010' < ver_end
AND crt_dt > '05/01/2010'
will return all versions in effect on 4/10/2010, provided those
physical rows were in the table no earlier than 5/01/2010.
Clearly, by using version dates along with create dates, effective
time versioning can keep track of both changes to policies and
other persistent objects, and also the creation and logical dele-
tion of versions that were not done on time.
