HomeCalendarFAQSearchRegisterMemberlistUsergroupsLog in

Share | 

 Time and Time Again: Managing Time in Relational Databases, Part 12: Foreign Keys in Version Tables

Go down 

Number of posts : 1850
Age : 55
Registration date : 2008-03-08

PostSubject: Time and Time Again: Managing Time in Relational Databases, Part 12: Foreign Keys in Version Tables   Sun 13 Apr - 20:04

In this article, and in Part 10 and Part 11, we have been discussing the issue of foreign keys and referential integrity in versioned tables. The issue arises because most modelers and database administrators (DBAs) seem to believe that if a change is made in a versioned table, and there are foreign keys pointing to that table, those foreign keys must also be changed. If they themselves are in a versioned table, and foreign keys in yet other versioned tables point to the rows they are in, versioned updates will ripple out to those other tables, creating a cascade update problem. In Part 10, we decided that our options are the options we always have for any problem. We can ignore the problem, avoid the problem or fix the problem. We also determined that we cannot ignore the problem because we are looking for an enterprise solution, one applicable to all databases. For databases containing small and/or infrequently updated versioned tables, we might be able to ignore the problem. But an enterprise solution must apply to all databases, regardless of size or frequency of update.

In Part 11, we suggested that the most perspicuous line of investigation was to begin by asking "What do foreign keys to versioned tables relate?" One answer is that they relate rows to rows. The "related-to" rows are the rows in versioned tables. The "related-from" rows may be either versions or non-versions. On the assumption that foreign keys relate rows to rows, whether or not any of those rows are in versioned tables, we developed one solution to the versioned foreign key problem. Here in Part 12, we develop a second solution to the versioned foreign key problem, also based on the assumption that those foreign keys still relate rows to rows. We will then develop a solution based on the assumption that foreign keys that point into versioned tables do not relate rows to rows, i.e., versions to versions, but rather relate objects to objects.

We will conclude our discussion of referential integrity involving versioned tables by distinguishing between the machinery of versioning and its semantics. The machinery is complex, and should be as invisible as possible to developers and users. But the semantics are the semantics of temporal databases, and once such databases are supported, those who write queries must understand those additional semantics in order to write unambiguous queries against those databases.

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. They also give a hint of version patterns not yet discussed.

The version of the client that was current when policy P138 was created is the first version. The version current when policy P704 was created is the second version. So with this rule, every foreign key pointing to a versioned table points to the version of the related object that was current when the foreign-key containing row was created. This rule applies both when the foreign key is a column in a versioned table, and also when it is a column in a nonversioned table.

Joins using such versioned foreign keys show what things look like, across joins, at the moment in time that the row from which we are joining was created. Therefore, using this rule, we can always follow a chain of foreign keys out from a particular row of interest, and bring together versions of the related objects as they were when that original row was created. We can, in other words, bring back data which is equivalent to a snapshot of that data taken at the moment the original row was created.

But sometimes that isn’t what we want. Sometimes, we want to join to the most current version of the related object. For example, consider a query which takes policy version [P138, 6/1/04], and joins it to the client who owns that policy. If we use the foreign key in that version, it will join to client version [C882, 5/23/04].

If this query was executed anytime between 5/23/04 and 9/30/04, it returned an upsell policy type of PPO. And during that period of time, this is the correct upsell policy type. But suppose this same query was executed on or after 10/1/04. In that case, the query returned an incorrect result, because from 10/1/04 forward, the current upsell policy type was HMO.

What are we to make of this? First of all, it is clear that this way of managing foreign keys in versioned tables will always produce correct results provided that, implicit in every join is the assumption that joined-to versions will be the version current when the joined-from row was created. But if this assumption is not valid, and in particular when what is desired is joins that return the then current versions of all joined-to objects, then using the foreign keys in versioned tables to make those joins is not guaranteed to return correct results.

How, then, can we write these other queries, the ones that want the current version of all joined-to versioned objects? The way we can do this is to write a query which specifies the joined-to object identifier – client number in this case – together with a clause that specifies the greatest version begin date not greater than the current date, and containing a {null} object end date. The “not greater than the current date” qualifier is needed to filter out future versions, versions that are not yet in effect at the time the query is executed. The "containing a {null} object end date" qualifier is needed to filter out an otherwise qualifying version that is for an object that has been deleted.

Something else that we must be aware of is that even though foreign keys to versioned objects point to rows current at the time the containing row is created, this does not guarantee that using these foreign keys will always return versions current when the joined-from row was first created.

Here's how this apparent anomaly can happen. Suppose that we also have an Employer table, which is also versioned. This table contains the employers of our clients. Now suppose we have a query which selects a policy, and then joins first to the client version current when it was created, and from there to the employer version current when that client version was created. This query may return employer data that was not current when that policy version was created.
Back to top Go down
View user profile
Time and Time Again: Managing Time in Relational Databases, Part 12: Foreign Keys in Version Tables
Back to top 
Page 1 of 1
 Similar topics
» After a long long time, we can have a clam sleep
» Time to invest in stock market: SC Securities
» Only 6 months more; time to discuss new SLFRS and its impacts to stocks
» Rupee hits all time low
» A Good Time to Trade in Gently Used Car -Is it Same SriLanka

Permissions in this forum:You cannot reply to topics in this forum
Job98456 :: Databases-
Jump to: