Surrogate keys are OK. I accept the theory, and in practice I tend to induce them even in FPD2.6, in the apps where natural keys were used for years. I probably didn't make myself completely clear - it's the question of, apart from surrogate keys, natural keys should behave as keys or not... though some of your remarks here give me an idea what could be done without much fuss.

>2) i.e. customers, they are sometimes acquired and users like to have customer ids that are easy to remember. ACME001 may be bought by ZENITH and the users would like the customer ID to become ZENITH001. With surrogate keys this totally permissible without referential integrity overhead.

Like here, we could use these natural keys just for user's convenience, but never actually use them in child tables; use them just to locate the parent record. So this has to behave as a key only within the customer table, to distinguish between customers (you wouldn't believe how many double names there are), but the only thing which must propagate into the child tables is the PK echoed in children's FKs. I like that.

>3) Often the "natural PK" for a table is comprised of multiple fields. When a PK is multiple fields the last two nromal forms come into play (4th and 5th). Those two normal forms are complex to say the least. Using a surrogate key cause all PKs to be only one field and therefore 4th and 5th NF don't apply.

When I was learning this theory (during my attempt to do a postgraduate course), we've stopped at the third, and were so happy that the theory confirmed what we felt right. Third normal form is what we all did, independent of language, except from propagating a couple of redundant fields for speed's sake (like the invoice date on a line item).

>6) As Dr. Codd once said, "itelligent keys aren't."

True. Back in old days of punched cards, a local carpet producer (George, listening?) had a verbose ID for carpets - two digits for the weaving type, three digits for the pattern, twice two digits for the size, four digits for the color pattern, two digits for the base/weave material used. The result was near to disaster - the operators soon learned the meanings of specific zones in the ID, and started punching non-existent combinations of them...