Jim,

>Business rules are best enforced through the business logic layer of an application and not through the database integrity rules. This is because over time business rules may change but database integrity rules do not change over time.

IMO there is vague border between the database integrity rules and the business rules. What one does regard business rules another regards a database integrity rule. For example the choice of a relationship to have cascading, restrict, nullifies (or ignore) RI rules often is based on business rules but are implemented as database rules.

I think that if business rules change, often the ehancement need to be done on the database because structures of tables might change too.

>A simple example: A customer table using the CustNumber (AlphaNumeric) as the PK. A major customer is ACME Corporation. The users decide they want that CustNumber to be ACME01 so it is easy to remember. 10 years go by and hundreds of invoices and payments have been entered for ACME. One day ACME is bought by ZENITH. The users want the CustNumber to be ZENITH01 so ti will remain easy to remember. With surrogate keys this is easy to accomodate without any mass updates to invoices or payments, with the nautral key the mass updates are needed.

It's a matter of what you want it to do. If an employee takes an invoice of two years ago (the system holds invoices until 5 years ago) and want to look up the sales of that customer, it comes to the conclusion that the customer does not exist, thinking: "there is an error in the system". He might expect to find this client in the database.

Another situation might be is that this "ZENITH" company, also was a customer in your database. How would you solve this issue ??

Therefore, an alternative might be that a new client is created, or (in the case the "ZENITH" customer already exist) as new delivery address is added.

The most common aproach i've seen is that the original customer is set as being discontinued and a new one is created. Also, it might be questioned, what is against the mass update as long as:
- PKs are not changed very frequent (x times a day)
- The 'mass' updates don't form a thread for your systems performance or integrity.

>Please, keep in ind, that for me a surrogate key is NEVER seen or used by a human. The humans still use the CustNumber to find the customer. The relationships with other tables are based on the surrogate. So in the scenario above I just let the users change the CustNumber and insure the uniqueness within the table. I have no need to involve myself with any other tables as they do not use the CustNumber at all.
>
>My choice to uniformly use surrogates is simply to provide consistency across all tables.

I know, you've stated this several times before. I'm just suggesting that this consistency might be burden, and might come across that the use of intelligent keys is prohibited. I think this is not true.

You seem to be very comfortable with the use of generated keys, however I think you should be carefull in preaching the forced use of it without telling the pro's and contra's.

Walter,

Walter