Databases Reference
In-Depth Information
whenever a change happens to it. But we may suppose that there
are other columns on the Policy table, columns not shown in the
example, and that the changes made on the update dates of
those rows are changes to one or more of those other columns,
which have been designated as columns for which updates will
be done as overwrites.
The other reason is that the data, as originally entered, was in
error, and the updates are corrections. Any “real change”, we may
assume, will cause a new version to be created. But suppose we
aren't dealing with a “real change”; suppose we have discovered
a mistake that has to be corrected. For example, let's assume that
when it was first created, that first row had PPO as its policy type
and that, after checking our documents, we realized that the cor-
rect type, all along, was HMO. It is now April. How do we correct
the mistake?
We could update the policy and create a new row. But what
version date would that new row have? It can't have March as
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
