Time and Time Again: Managing Time in Relational Databases, Part 19 - RI and Temporal RI

InfoManagement Direct, February 1, 2008

Tom Johnston, Randall Weis

This installment should have been the continuation of our presentation of physical implementations of the version patterns discussed thus far. It should have presented a physical implementation of Version Patterns 4 and 5. However, because editors are just plain tough when it comes to deadlines, and because of some personal exigencies with the authors, we are unable to stick to this logical sequence. Instead, in this article, we will consolidate and extend some of the things we have learned about the semantic constraints that apply to versions, and especially to what referential integrity means when applied to versions.

In terms of the roadmap presented in Part 13, this means that we are putting Step 2 aside, for the moment, and moving on to Step 3, the semantics and implementation of Version Pattern 6. The first part of those semantics which we wish to discuss is what we will call "temporal referential integrity" (temporal RI).

A glossary is included at the end of this article. As we have been writing this series, we have developed a set of terms which have been defined more by means of introducing and using them than in any more formal way. Of course, the later Wittgenstein (someone whose name C. J. Date likes to invoke) did indeed say "Meaning is use," but it is well-known (by philosophers, at least) that the later Wittgenstein is notoriously difficult to interpret and, consequently, correspondingly inappropriate as a source of quotations taken out of context. This glossary, like dictionaries when they are periodically undergoing revisions, takes usage to be prescriptive of meaning, but not identical to it. And in so doing, it and we part company with the later Wittgenstein for whom most philosophical problems need to be dissolved, not solved.

RI and Temporal RI

The referential integrity (RI) that we all know and love is RI that the database management system (DBMS) can enforce, based on declarations we make to it when we submit our data manipulation language (DML). It is RI against nonversion tables. For clarity's sake, we have been calling non-version tables "object tables," not in the object-oriented sense, but rather in the sense of things that persist through time, things that versions are versions of. Thus, a Customer table is a table of customers, which are instances of a certain kind of object, and which persist through time. But a Customer Version table is not a table of customers; it is a table of chronologically sequenced, nonoverlapping, time slices of customers.

As we continue, we will use the term "object RI" when we think the adjective is needed for clarity, and simply "RI" when context makes it clear that we are talking about object RI. Finally, we will always use the term "temporal RI" when talking about RI as it applies to versions.

Object RI

Here is the definition of object RI:

If there is an object RI dependency from table Y to table X (not necessarily distinct), then no state of the database is valid in which any row in table Y is not foreign key-linked to a row in table X. No inserts, updates or deletes are valid that would transform the database into such a state.

The RI machinery of the DBMS insures this by carrying out the following checks. We assume throughout this discussion, unless otherwise noted, that all foreign keys are defined as non-nullable.

1. When inserting a row into object table Y, that row must reference a row in object table X that already exists in the database (or that is being inserted as part of the same atomic transaction).
   
   
2. When updating the foreign key to a row in object table X, from a row in object table Y, that new foreign key must reference a row in X that already exists in the database (or that is being inserted as part of the same atomic transaction).
   
   

   Page 1 of 5.

   1 2 3 4 5 next