In Part 10, we presented a concern that many modelers and database administrators (DBAs) have expressed about what happens to foreign keys when a new version of an object is created. We said that "in general, the concern is that if a new version of an object is inserted into a version table, then all foreign key references to the version it replaced must be updated to point to the new version. Furthermore, if those updates take place in tables which are themselves versioned tables, then those updates create additional new versions and all foreign keys that point to them must also be updated. And so a single versioned update can cascade out to affect many other versioned tables, ending only when a cascade chain reaches either a) a non-versioned table, or b) a versioned table which does not itself contain any foreign keys."
We also noted that there is a potential cascade problem when an object is deleted. Note two key phrases here: a) "when a version is replaced," and b) "when an object is deleted." Versions are not updated; versions are replaced. It is objects which are updated in the process of one version replacing a previous one. Also, versions are not deleted; objects are deleted by placing a deletion indicator on the current version of the object, and also on any future versions on the database at the time of the deletion.
Chart of Version Patterns
Before continuing, we insert here the current chart of version patterns. References back to earlier patterns are happening pretty frequently now, and we hope this will help the reader recall what those prior version patterns are.
Chart of This Series
Before continuing, we insert here a chart listing the installments to date in this series, and a brief description of what each one is about. As this series grows longer, the back references to earlier installments will only increase, and we think that having a chart like this at hand will be helpful.
What Do Foreign Keys Relate? The Issue
In non-versioned tables, foreign keys relate objects. Each row in a Client table represents a client. Each row in a Policy table represents a policy. What a foreign key from a Policy table back to a Client table does can be expressed in two different ways. First, we can say that it relates the row representing that policy, which contains the foreign key, to the row representing the client for that policy. Secondly, we can equally well say that it relates that policy to that client.
The first way of saying what the foreign key does refers to the data; the second way refers to the things which that data represents. The first way, from the point of view of data management, is physical; the second way is logical or, more precisely, semantic.
In non-versioned tables, there is a one-to-one pairing of things we are interested in (clients, policies) with data that represents them (rows in tables). Because the mapping between what is represented and its representation is one-to-one, we don't need to pay much attention to the distinction. We can talk about what foreign keys relate in terms of either objects or rows.
