Time and Time Again: Managing Time in Relational Databases - Part 22: Original and Temporal Deletes

Note: A glossary of technical terms used in these articles can be found on the Web sites of the authors. The URLs are MindfulData.com and InbaseInc.com.

This glossary is being constructed as a controlled vocabulary. By that we mean that any expression involving temporal concepts which is used in the definition of a glossary entry must itself be a glossary entry.

One reason for being this careful with definitions is that this kind of rigor must be applied to definitions before they can be "ontologized," i.e. expressed in a formal notation like predicate logic. That, in turn, is what is needed to make definitions available for manipulation by software-realized inferencing engines.

Another reason for being this careful with definitions is that this is the kind of rigor that must be applied to any set of technical definitions before we can say with confidence that we know what we mean, and that we mean what we say, no more and no less.

In our previous article, we introduced the distinction between original and temporal transactions, and began by discussing original and temporal deletes. This article completes our discussion of delete transactions. We will then go on to discuss insert, update and upsert transactions.

These discussions describe the transformations required to map original transactions onto temporal transactions. This mapping is the "database update side" of the encapsulation of the complexities of temporal data management which, in Part 1, we said was an essential part of our approach.

These same discussions also provide the insight necessary to decide whether we should attempt to enforce temporal referential integrity (RI) on the updates themselves, or instead defer that integrity checking until data is retrieved. With respect to the deferral option, we will repeat what we said about it in the previous article:

It's easy to disparage [the deferral 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.)

From the Previous Article

For the sake of continuity, we repeat the following material from the previous article.

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.

An "original" transaction is a transaction created or authorized by the business. A "temporal" transaction is the result of translating an original transaction into a transaction against a versioned object.

To the business, when they are issuing a delete against an object, they don't care if the object is versioned or not, and indeed may not know. They are directing us to remove the object from the database. Thus, an original delete transaction doesn't need to specify a version, just the object being deleted and the date the delete is to take effect. This is illustrated in Figure 1.