Databases Reference
In-Depth Information
Bi-temporal tables contain both kinds of information. Their
rows tell us what we once asserted as true, currently assert as
true, or may at some future time assert as true, a statement
about what an object used to be like, is like right now, or may
be like at some time in the future.
Because each object is represented by exactly one row in a
non-temporal table, when updates are applied, those updates
overwrite the data that was there before the update. Such
updates are called “updates in place”. But one problem with
updates in place, of course, is that they lose information. They
lose the information about what the object used to be like, about
what it was like before the update. They lose historical informa-
tion because they overwrite data.
Historical data can usually be found somewhere, of course, in
archives and transaction logs if nowhere else. But if it is impor-
tant to be able to quickly and easily access data about what
objects used to be like, either by itself or together with data
about what those objects are like now, then keeping that data
in the same table that also contains data about the current state
of those objects makes a lot of sense.
If we don't keep historical and current data in the same
table, then query authors who need that data will need to be
aware of the multiple table and column names, and the multi-
ple different locations, where different subsets of historical data
are kept; and, as we know, they often are not. Even if aware of
all the places from which they will have to assemble the histor-
ical data they are interested in, they will also have to know
which of possibly redundant copies of that data is the most cur-
rent, and which the most reliable and complete; and, as we also
know, they often don't.
They may need to rewrite queries, changing table and col-
umn names prior to pointing them to whichever copy of that
data is chosen as the target for those queries; and in doing so,
as we all know, they often make mistakes. And when tables of
historical data are not kept column for column union-ably paral-
lel with the corresponding tables containing current data, which
is often the case, then the job of query authors becomes even
more difficult and error-prone. They won't be able to simply
copy production queries and change names. They will have to
write new queries, perhaps joining data that the production
queries did not have to join, perhaps assembling intermediate
results and then combining those intermediate results in various
complicated ways. In short, they may very quickly be taken into
the realm of SQL queries that all but the most experienced query
authors have no business writing.
