At the conclusion of our previous article, we said that there are two options for enforcing temporal integrity constraints on a database. One is to enforce the constraints at the time of update so as to prevent violations from being persisted in the database. The other is to let violations occur, but to filter them out at retrieval time. This would be done by means of views against which all normal queries would be written.
It's easy to disparage the latter option by describing it as letting temporal "garbage" onto the database, and then trying to hide that garbage from queries. But this is indeed the approach we have taken on temporal databases that we have designed, built, put into production and supported, and we had good reason for doing so. Like almost all database professionals, we were concerned that there would be an overwhelming volume of cascade updates if we tried to enforce temporal RI at time of update, especially when the update involves the delete of a versioned object that was the "parent" of possibly many other objects, in many other tables. (See the introduction to Part 10 for a more complete description of this "cascade update" fear.)
We believe it is time to re-examine this fear, and see if the performance hit for enforcing temporal RI on update transactions is as great as we have feared. We assume here that for as long as data storage continues to involve spinning disks, cylinders and mechanical movement of read/write heads, to get to required data, we can safely simplify our performance issue and treat it as a matter of how many physical I/Os, how many block-level transfers between a storage device (or its cache) and main memory, are required to complete a temporal RI validation. With this in mind, let us now examine two things – the logic of transactions against versioned objects, and the I/O that is required for those transactions.
Temporal RI Reformulated
To more explicitly allow for the query-time enforcement of temporal integrity constraints, we will slightly amend the definition of temporal RI given in Part 20, as follows:
If there is a temporal RI dependency from table Y to version table X (not necessarily distinct), then no exposed state of the database is valid in which any row in table Y is not object-RI linked to a row in table X, or in which the effectivity time period of a row in table Y is not wholly contained within the effectivity time period of its parent row in table X. No queries are valid which would expose such a state.
There are three changes to the original definition:
- "Version table Y" changed to "table Y." As explained last time, we allow both versioned and non-versioned tables to be object-RI dependent on a versioned table.
- "State of the database" changed to "exposed state of the database." This allows for the option of enforcing temporal RI on queries rather than on updates.
- "No inserts, updates or deletes are valid that would transform a database into such a state" replaced by "No queries are valid that would expose such a state." The replaced sentence was appropriate for update-enforced temporal RI. The replacing sentence is appropriate for either update- or query-enforced temporal RI.
We will assume, in the following discussion, that we are dealing with update-enforced temporal RI. In later articles, we will discuss query-time enforcement.
Note: before proceeding, please note that the glossary introduced a couple of installments ago is not included in this article, nor will it be included in future articles. The reason is that the glossary is already over 3,000 words long and likely to grow to several times that size.
