Martin,

>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?

So the problem lies in modifying table structure and programlines. Well, shit happens. You can't avoid this entirely with surrogate keys (you still have to modify table structure and programlines). Maybe when this occurs it is a good time to declare the existing PK (former intelligent invoicenumber) as surrogate and add a new field for the new representation of the PK (just as much work as you would have with surrogaste keys).

You've pointed out a significant advantage of surrogate keys in most (R)DBMSs (some use domains which makes it possible to address all related fields in one place). Though in the same line there is a significant disadvantage when forced to use surrogate key. See the message to chris in this thread regarding the city issue.

Here the disadvantage of using surrogate keys might be even more than the advantage in your case.

>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-).

OTOH they're pretty static, and if changed a cascading update will do.

>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.

We strongly disagree on this point.

Walter,