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