Databases Reference
In-Depth Information
Jan10
Jan
2010
INSERT INTO Policy (BK, ver_beg, client, type, copay, ver_end, crt_dt, updt_dt)
VALUES ('P861', 'Mar10', 'C882', 'HMO', '$15', '9999', CURRENT_DATE)
Jan
2011
Jan
2012
Jan
2013
Jan
2014
BK
ver-dt
client
type
copay
ver-end
crt-dt
updt-dt
P861
Mar10
C882
HMO
$15
9999
Jan10
{null}
Figure 4.9 Effective Time Versioning: After a Proactive Insert Transaction.
for version tables—a time period which we call assertion time,
and computer scientists call transaction time—effective time
versioning adds a single date. Next, instead of adding this date
to the primary key of the table, as was done with the version
begin date, this new date is included as a non-key column.
With effective time versioning, the version begin and end dates
indicate when versions are “in effect” from a business point of
view. So if we used the same schema for effective time versioning
as we used for temporal gap versioning, we would be unable to
tell when each version physically appeared in the table because
the versioning dates would no longer be physical dates.
That information is often very useful, however. For example,
suppose that we want to recreate the exact state of a set of tables
as they were on March 17
th
, 2010. If there is a physical date of
insertion for every row in each of those tables, then it is an easy
matter to do so. However, if there is not, then it will be necessary
to restore those tables as of their most recent backup prior to
that date, and then apply transactions from the DBMS logfile
forward through March 17
th
. For this reason, IT professionals
usually include a physical insertion date on their effective time
version tables.
Once the proactive insert transaction shown in
Figure 4.9
has
completed, then at any time from January 1
st
to the day before
March 1
st
, the following filter will exclude this not yet effective
row from query result sets:
WHERE ver_dt <¼ Now() AND Now()< ver_end
But beginning on March 1
st
, this filter will allow the row into
result sets. So the use of this filter on queries, perhaps to create a
dynamic view which contains only currently effective data,
makes it possible to proactively insert a row which will then
