representing that object during some period of its existence. The
one non-temporal row, and the set of version rows, cover exactly
the same period of time.
But basic versioning is the least frequently used kind of
versioning in real-world databases. The reason is that it pre-
serves a history of changes to an object for only as long as the
object exists in the database. When a delete transaction for the
object is applied, all the information about that object is
removed.
One type of versioning that is frequently seen in real-world
databases is logical delete versioning. It is similar to basic
versioning, but it uses logical deletes instead of physical deletes.
As a result, the history of an object remains in the table even
after a delete transaction is applied.
Logical Delete Versioning
In this variation on versioning, a logical delete flag is included
in the version table. It has two values, one marking the row as
not being a delete, and the other marking the row as being a
delete. We will use the values “Y” and “N”.
After the same insert and the same update transactions, our
non-temporal and logical delete version tables look as shown
in Figure 4.5.
We are now at one clock tick before December 2010, i.e. at
N
ovember 2010. Although
we have chosen to use a one-month
clock in our examples primarily because a full timestamp or
even a full date would take up too much space across the width
Nov10
Jan
2014

Aug10
updt-dt
crt-dt
copay
copay
del-flg
N
N
N
type
type
client
client
Figure 4.5 A Logical Delete Version Table: Before the Delete Transaction.
Chapter 4 THE ORIGINS OF ASSERTED VERSIONING: IT BEST PRACTICES
83
of the page, a 1-month clock is not completely unrealistic. It
corresponds to a database that is updated only in batch mode,
and only at one-month intervals. Nonetheless, the reader should
be aware that all these examples, and all these discussions,
would remain valid if any other granularity, such as a full
timestamp, were used instead.
Let us assume that it is now December 2010, and time to apply
the logical delete transaction. The result is shown in Figure 4.6.
H
owever,
the non-temporal table is not shown in Figure 4.6,
or in any of the remaining diagrams in this chapter, because our
comparison of non-temporal tables and version tables is now
complete.

VALUES (‘P861’,CURRENT_DATE, ‘C882’, ‘PPO’, ‘$20’, ‘Y’ )
BK
P861
P861
P861
P861
Jan10 C882
C882
C882
C882 PPO
PPO
HMO
HMO
type copay
$15
$20
$20
$20
N
N
N
Y
del-flg
ver-dt
client
May10
Aug10
Dec10
Figure 4.6 A Logical Delete Version Table: After the Delete Transaction.
84

an object the business has encountered before.
Let’s look a little more closely at this important point. As diffi-
cult as it often is, given the ubiquity of unreliable data, to support
the concept of same object, there is often much to be gained. Con-
sider customers, for example. If someone was a customer of ours,
and then for some reason was deleted from our Customer table,
will we assign that person a new customer number, a new identi-
fier, when she decides to become a customer once again? If we do
so, we lose valuable information about her, namely the informa-
tion we have about her past behavior as a customer. If instead
we reassign her the same customer number she had before, then
all of that historical information can be brought to bear on the
challenge of anticipating what she is likely to be interested in
purchasing in the near future. This is the motivation for moving
beyond logical delete versioning to the next versioning best prac-
tice—temporal gap versioning.
Chapter 4 THE ORIGINS OF ASSERTED VERSIONING: IT BEST PRACTICES
85
Temporal Gap Versioning
Let’s begin by looking at the state of a temporal gap version
table that would have resulted from applying all our transactions
to this kind of version table. We begin with the state of the table
on November 2010, just before the delete transaction is applied,
as shown in Figure 4.7.
We notice, first of all, that a logical delete flag is not present
on
the table. We will
see later why it isn’t needed. Next, we see
that except for the last version, each version’s end date is the
same as the next version’s begin date. As we explained in

2012
Jan
2011
Jan
2010
BK
P861
P861
P861
Aug10
May10
Jan10
ver-dt
C882
C882
C882 PPO
HMO
HMO
type
client
copay
$15
$20
$20
9999
Aug10
May10
ver-end
Figure 4.7 A Temporal Gap Version Table: Before the Delete Transaction.
86

see, the ways in which it falls short
of full bi-temporality are due to two features. First, instead of
adding a second a pair of dates to delimit a second time period
Dec10
Jan
2014
Jan
2013
Jan
2012
Jan
2011
Jan
2010
UPDATE Policy
WHERE BK = ‘P861’ AND ver_beg = ‘Aug10’
SET ver_end = ‘Dec10’
ver-dt
BK
P861
P861
P861 Aug10 C882 PPO
HMO
HMO
type
$20
$20
$15
copay
ver-end

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
comple
ted, then at any tim
e 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
Jan10
Jan
2014
Jan
2013
Jan
2012

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
BK
P861
P861 May10
Mar10
C882
C882 HMO
HMO

its version date because that would create a primary key conflict
with the incorrect row already in the table. But if it is given April
as its version date, then the result is a pair of rows that together
tell us that P861 was a PPO policy in March, and then became an
HMO policy in April. But that’s still wrong. The policy was an
HMO policy in March, too.
We need one row that says that, for both March and April,
P861 was an HMO policy. And the only way to do that is to over-
write the policy type on the first row. We can’t do that by creating
a new row, because its primary key would conflict with the pri-
mary key of the original row.
Effective Time Versioning and Retroactive Inserts
and Deletions
Corrections are changes to what we said. And we have just
seen that effective time versioning, which is the most advanced
of the versioning best practices that we are aware of, cannot
keep track of corrections to data that was originally entered in
error. It does not prevent us from making those corrections.
But it does prevent us from seeing that they are corrections,
and distinguishing them from genuine updates.
Next, let us consider mistakes made, not in the data entered,
but in when it is entered. For example, consider the situation in
which there are no versions for policy P861 in our version table,
and in which we are late in performing an insert for that policy.
Let’s suppose it is now May, but that P861 was supposed to take
90
Chapter 4 THE ORIGINS OF ASSERTED VERSIONING: IT BEST PRACTICES