Databases Reference
In-Depth Information
But if we had a PERIOD datatype, and SQL extensions and
indexing methods that could recognize and manage that
datatype, then all the Allen relationships among time periods
could be easily expressed, and the very important [
excludes
]
relationship could be enforced by means of a unique index.
Lacking that future technology, and the standards needed to
insure interoperability across different vendor implementations,
the AVF contains its own code that effectively turns pairs of dates
into a user-defined PERIOD datatype.
Temporal Primary Keys
A third issue related to changes in the SQL standard is sup-
port for temporal primary keys. With those temporal extensions,
we will be able to declare a temporal primary key to the DBMS
and, by the same token, declare temporal foreign keys as well.
But what is it we will be declaring? Temporal tags added to phys-
ically unique identifiers of rows of otherwise non-temporal
tables? Or something more?
If a SQL standard for bi-temporality, when we eventually have
one, is a standard for adding two temporal tags to rows in other-
wise non-temporal tables, and providing a PERIOD data type
and Allen relationship operators to manage the data thus tagged,
then most of the semantics of bi-temporality will have been left
out of that standard, and left up to developers. The managed
objects of temporal data management are not physical rows in
physical tables. They are collections of one or more rows which
represent temporally delimited claims about temporally
delimited statements about what real-world persistent objects
were like, are like, or will be like.
As long as every database table contains one and only one
row for each instance of the type indicated by the table, it is easy
to forget about the semantics and concentrate on the mechan-
ics. Primary key uniqueness is mechanics; its role in eliminating
row-level synonyms—and its failure to address the problem of
row-level homonyms—is the semantics that are easily and
almost always overlooked. Foreign key referential integrity is
mechanics; its role in expressing existence dependencies among
the objects represented by those rows is the semantics that are
easily and almost always overlooked.
It has been this one-to-one correlation between rows and
the objects they represent that has made it easy to give short
shrift to semantics, and to then get right down to what really
fascinates engineering types—the mechanics of making things
work. But once we attempt to manage both the epistemological
