Hi Mike,

>You are correct. I meant surrogate / meaningless keys in most of this discussion.

>>Although I use integer surrogates in 90% of my tables, it does not mean it is always the most logical choice.

>If 90% of the time you are using integer surrogate keys, have you examined the other 10% to see if there is significant benefit to sticking with meaningful keys? You provoke me to do that once in a while, and I keep deciding there's rarely a reason to use meaningful keys.

I think look it from another viewpoint. Does an surrogate key add some benefit? If not, why are you using it ?

>That's the haunting part. It depends! If the Postal Code Validation Table only contains postal codes, then you may be correct. If however, the PCVT contains postal codes, country, format string, long-lat data, etc. then surrogate keys make more sense.

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

I think this postalcode example is not the only example. In any system where you might want to extend the application with external data from 3rd parties and other applications, you might find more examples of cases where the same decision has to be made.

Walter,