Re: Surrogate keys (Was: enums)

Dann Corbit wrote:
> 
> When the data changes, the problems generated are not just due to
> repercussions related to the child and parent tables related through the
> primary key.
> 
> Someone has an invoice, and they call in with a question. A combination
> of their name and address was used as a primary key.  They moved, and
> sent in a forwarding address.  The DBA was smart enough to design the
> database to cascade results, so that there are no orphan records and we
> have not compromised the structure of the database.
> The customer calls in with a question about an old invoice.
> "We have no record of that transaction."

Aside:
Even if not using name+address as a primary key, a separate record 
should be kept of these details *at the time of the invoice* otherwise 
you'll never be able to match up a printed invoice with its digital 
source. Usually of course this is by inv_name, inv_address columns in 
the invoice header, but it could be by some fancy temporal versioning on 
client details.

--   Richard Huxton  Archonet Ltd