Hi Jim,

You've made it clear for a long time now that you prefer surrogate keys everywhere. And your Groege Foreman example is excellent for the argument, and I would find myself having to use one or more there too.

It's just that, in general, I personally have a preference to stick with the "natural" keys/fields wherever possible. I have the opinion that this practise makes me more careful with by DB design, aids the understanding of my apps by others, keeps record sizes and tables sizes down to more 'natural' sizes, keeps recovery steps simpler and less numerous, etc.

And I do not use filters of any kind (except in coded statements), least of all in indexes.

Cheers,

Jim N

>Jim,
>
>I know you know this but I just want to put it in the thread. The of K1, K2, ..., Kn is relational algebra that simples says a PK may be comprised of one or more fields.
>
>My contention regarding a surrogatr eky is this, if I have a table of people (not employees or customers so there is no truely natural key field) and their addresses, one would think the combination of all fields would provide a unique identifer. I mean what is the chance that two poeple would have the same name and the same address, and the same phone number etc..?
>
>Well, if I ever neede to put Gerogre Forman and his sons in that table then I would have five duplicate recors that are not, in fact, duplicates. Theya re different records for different people who happen to have the same name, address, phone, etc..
>
>Using a surrogate key here solves the problem. I am also a proponent of keeping it simple. Surrogate keys are always a single field, simple. If I use a surrogate key in one place I want to use them everywhere, consistency is simple.
>
>So, my conclusion, forget the natural keys and use surrogate keys everywhere whehter one thinks they need it or not. Every table in every database will be the same design as far as the PK goes.
>
>Another plus of surrogate keys, there is never any need to go beyond Boyce-Codd NF. After 3rd NF, insure 3rd NF for any feasible candidate keys in the table and you're done. No need to even think about 4th and 5th NF's since they only apply to multifield PKs.