Databases Reference
In-Depth Information
multiple rows all having the same identifier and the same first
two dates.
At least, that's the idea. In fact, as we all know, a five-column
primary key allows us to keep any number of rows in the table as
long as the value in just one column distinguishes that primary
key from all others. So, for example, the DBMS would allow us
to have multiple rows with the same identifier and with all four
dates the same except for, say, the first begin date.
This first example of bi-temporal data shows us several
important things. However, it also has the potential to mislead
us if we are not careful. So let's try to draw the valid conclusions
we can from it, and remind ourselves of what conclusions we
should not draw.
First of all, the third illustration in
Figure Part 1.1
does show us
a valid bi-temporal schema. It is a table whose primary key
contains three logical components. The first is a unique identifier
of the object which the row represents. In this case, it is a specific
customer. The second is a unique identifier of a period of time.
That is the period of time during which the object existed with
the characteristics which the row ascribes to it, e.g. the period of
time during which that particular customer had that specific
name and address, that specific customer status, and so on.
The third logical component of the primary key is the pair of
dates which define a second time period. This is the period of
time during which we believe that the row is correct, that what
it says its object is like during that first time period is indeed
true. The main reason for introducing this second time period,
then, is to handle the occasions on which the data is in fact
wrong. For if it is wrong, we now have a way to both retain the
error (for auditing or other regulatory purposes, for example)
and also replace it with its correction.
Now we can have two rows that have exactly the same identi-
fier, and exactly the same first time period. And our convention
will be that, of those two rows, the one whose second time period
begins later will be the row providing the correction, and the one
with the earlier second time period will be the row being
corrected.
Figure Part 1.3
shows a sample bi-temporal table con-
taining versions and a correction to one of those versions.
In the column
ed
2
, the value 9999 represents the highest date
the DBMS can represent. For example, with SQL Server, that date
is 12/31/9999. As we will explain later, when used in end-date
columns, that value represents an unknown end date, and the
time period it delimits is interpreted as still current.
The last row in
Figure Part 1.3
is a correction to the second
row. Because of the date values used, the example assumes that
