Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
led-in-the-hed
Starting Member
1 Post |
 Posted - 2007-08-31 : 14:10:18
|
| 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. |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-01 : 04:40:22
|
We use Archive tables, populated by triggers on the corresponding "Master Table", that copy the "before" data into the archive whenever a change is made.No attempt to differentiate between "Fixing a typo" or "substantive change" - although we could eithera) Allow a Flag on Archive records to indicate if the change was Substantiveb) Allow Delete of trivial Archive records [Shudder!]which would mean that we could "hide" irrelevant/trivial archive records (which doesn't effect the "Delta" between any given two moments in the history chronology, of course)The Key benefit, for us, is that this is automatic, and guaranteed. Change the record and it gets "audited" in the Archive table.In practice we don't seem to have too much difficulty with this - support staff look in the Archive / History when they want to see HOW a change came to be. They aren't too bothered by the fact that there may be several records with trivial changes, they can find the When/How/Who of the relevant change they are hunting for, and take remedial action as appropriate.The multiple-registration is a much greater concern for us.On eCommerce web sites [which is what we do] we used to have the "Register before you can do anything" approach. Folk generally logged in, rather than re-registering. Exceptions being folk that had changed email address, couldn't find their login, or even if they could remember their old email address have now forgotten their password and cannot be emailed it because the email address is now defunct.Now we have a "Login or provide address details" at checkout. Very little incentive to login, just bang your address in again. Google toolbar will even do it all for you at the touch of a single button.Result? Lots of duplicate registrations, increased cost sending out shed-loads of paper catalogues to punters. Punters cannot find their order history because they have umpteen separate accounts.And we have to put lots of effort into coalescing accounts, merging order history - and even unmerging account histories when the system gets it wrong  Progress?Kristen |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-09-04 : 08:39:37
|
| I use either a full archive table as Kristen describes, or a smaller format that just describes the modification as a text string, depending upon the application requirements.e4 d5 xd5 Nf6 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-09-04 : 09:09:42
|
| "smaller format that just describes the modification as a text string"I've seen lots of applications that provide the info to the user in this format, never actually thought to store it in that format though!Kristen |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-09-04 : 09:40:08
|
| I even saw one application that stored the modification as XML, because the interface has a user screen that would read it to show the user the record's change history. I think it actually worked out pretty well.e4 d5 xd5 Nf6 |
|
|
|
|
|
|
|
|
The passing away of the XREF column