Hi, Walter.

I'm joining the surrogate band, I'm afraid. The debate and your position has been very useful for me in convincing me even more about its usefulness and the need to use it consistently.

>>>So we agree that the FORCED use of surrogate keys is not wise in all circumstances ??
>>
>>I do not know what you mean by FORCED. I have yet to come across a case where I would not use surrogate keys.
>
>Well, what about the case of invoice numbers ??? Are you consistent and add a surrogate key, even though a autonumber invoicenumber would do ? I'm not, because the surrogate key would add no benefit to the current scheme.

I think surrogate keys are great when unexpected changes comes. And I mean TOTALLY unexpected.

Here in Argentina, since a few years ago, you have invoice number parted on two (an invoicing place of 4 digits, and the sequential number), AND our geniuses at the local IRS invented a letter that identifies different invoice types depending on the customer ("A" for companies, "B" for individuals, and another ones that would be quiete difficult to explain).

So, just imagine what happened to those who weren't using surrogates. Even worst, as the invoices have to be pre-printed, debit and credit notes use the same sequential number that invoices, EXCEPT that you print a different form.

So the way to locate an AR document is using doc_type + invoicing_place + inv_number + damned_letter. Not a very nice natural key, uh?

I read here also that state abbreviation makes a good natural key because it isn't expected to change. FALSE! As you know, USA is not the only place where people use bussiness apps, and this is a total lack of vision. Different countrys have different codign standard for cities, counties, and such, and in many of them those change from time to time -somewhere even monthly 8-).

Being in a global market (in southamerica is better for you to try to reach other countries because one's own is ussually a small market or at many times a though one), we qucickly discovered that natural keys doesn't endure very much.

Local particularities, differences in fiscal and business customs, etc, are a STRONG point to FORCE use of surrogate keys, in my humble opinion.