Hi Mike,

>There is a benefit to having a single technique that can be applied to all cases, because it means less coding overall. I'm not saying I would never use meaningful keys, just that I'd only use them if there was a really significant benefit to doing that. If you have to stop and consider which technique to apply at every circumstance, you'll be spending more time deciding and less time developing.

OK, I think there is a nuance to be made here. When I don´t think there is any objection in using a surrogate key, I´ll just implement this. However If I inmediately recognize that it is more of a problem than a benefit (See postalcode example), then I don´t. A decision should not take hours to make, but just a few seconds.

>>The point here is that such tables are often provided by 3rd parties without any surrogate key. The postal code often is the PK.

>You cannot expect a 3rd party to provide their primary key since we can't even agree on PK implementations and we use the same database. There is nothing wrong with a process to import the 3rd party data into your system.

Exactly the point.

>>You can also imagine that you start the application without a Postalcode checking table and just store the postalcode and that´s it. When you decide you want to add a postal code checking routine to your application, are you going to convert all existing FKs into surrogate keys ? I think not. You´ll use the existing postal code as a FK to the purchased postal code table in which the postal code is the PK. Any purist that now wants to change the postalcode field into a surrogate foreign key is haunting gosts I you´d ask me.

>Possibly. If however, you recognize that users may enter postal codes badly, you might provide a mechanism to keep the good ones and discard the bad ones. A foreign key can mean changing a single postal code is automatically applied to many records.

I´m not sure what you´re trying to say here. The 3rd party postal code table is just a lookuptable. It is used for checking if the users have entered a bad postalcode.

As in the case of changing the postalcode in the lookuptable and automatically changing the postalcodes in the child tables. This another good reason not to use an surrogate PK here.
In the surrogate key implementation the change of the postalcode in the postalcode table would automatically change the postalcode of potentially many childrecords. This normally is something you want to prevent in the real world. With the surrogate key implementation you cannot use RI to enforce a update restrict, since the postalcode itself is not involved in the RI relation. You´ll end up in writing code which tend to be buggy anyways.

With the intelligent key approach it is very simple to prevent users to make changes in postalcodes in the postalcode table. Just set a RI update restrict rule.

Many will argue that if you want to be able to rename a PK (CK) the surrogate key is the best solution. While it is true that it is the fastest way to make changes to the logical PK, you can do the same with a cascading update in the intelligent key approach. While I inmediately admit that this might be far slower because of the large number of updates that have to be performed, you have to ask the question how often you want to do the renaming.

Walter,