The Passing Away Of The XREF Column

When I was but a young developer, I saw many databases that made use of XREF columns to manage the revision history of records; if a customer had a change of name, a new customer record was created with the new name and the old record was given a cross reference to the new record. Often the user was also required to enter a reason for the cross reference.

This was a very practical paradigm, easily understood by users as it mirrored business practices that were followed regarding paper records. It presented some issues for reporting however, as following xref chains did not translate easily into set based grammars.

Fast forward to the modern times. The prevalent approach has been the use of effective dating and transactional dating, which -- while possibly superior from an algebraic standpoint -- has its own drawbacks in application.

One weakness is the lack of a paradigm or strong metaphor. This often leads to imperfect implementations where data entry errors cannot be distinguished from more meaningful changes, or historical reporting that presents incorrect information because of the multiplicity of dates.

In addition, of particular interest is that often no facilities exist in modern systems to handle real world situations involving duplicate records: a customer is entered into a system, changes names, and is entered into the system again under the new name. The error is discovered some time later, after invoices or statements may have been sent to both customer records, but no function exists within the system to indicate that both records refer to the same entity. It becomes an issue for DBAs to deal with and resolve.

I would be interested to hear any comments or rebuttals from individuals as to which technique they feel is superior, and additional weaknesses or strengths of the two techniques.