>I have often noticed that the practice of using ONLY surrogate keys, which is regarded by many (including you) here as fundamental to database design, is not a part of the most basic database examples given in the first few chapters of a book. Think about the most basic example of a database we are given: customers and their orders. They always tell you to relate them on customer number. It would be pretty dumb for them to assume that customer numbers are generated by your Fox program, so it amounts to using meaningful primary keys. Even Tastrade, offered by some people here as an example of a good framework, relates customers and orders on a Customer ID which is obviously derived from the customer name. I guess that is Microsoft's way of not admitting that their RI is not 100% reliable.

Bret, when a customer tells me they want to relate their orders by a user-entered key, I nod politely, add their key - and link by the surrogate, along with some checks on the user-key to make sure it's unique and added to the appropriate record. Most of the time what the WANT is to use their key as a lookup field. No problem. It rarely fails that their 'unique' system allows duplicates or blanks at some point in their system. Also, they LOVE to 'adjust' these keys as customers change names, locations, etc.

I'm reviewing a system for a client that uses phone numbers as unique keys in a 3-tiered set of data. Some of those customers have changed their phone# 10 times, but their key is still the original from 10 years back. Upkeep is VERY difficult, since that's the ONLY way they can search except by a contact's first name. And the contact probably left last year also.

Barbara