Hi, Walter.

>>Of course one have to modify some code, but if you are using surrogates, you just need to change the places where the complete invoice ID is used, and you don´t have to do ANY change to the related tables and their management.
>
>Changing the related tables won't be the biggest a problem IMO. Broken views and program lines are. They occur in both using surrogate and intelligent keys.

Changing the related tables IS a problem. You have to consistenly add/change all the affected fields, indexes, AND the code that handles this. Using surrogates you have to update JUST the code that handles the patent table search trough the candidate key. The rest is left unchanged.

I know very good about this as the header Invoice table in my ERP suite is related to more than a dozen tables. That sort of changes would be overwhelming and incredibly error prone without surrogates.

>To what are you refering ? Do you agree that in the "city" case an intelligent key solution (case A) might be the best choice ??

No. I disagree here too. If you need your system to be used everywhere, you can't use any sort of intelligent key. ZIP codes are different in every country (indeed they are not called ZIP codes in most places, and in many it doesn't even exist).


>All these issues can be handled with intelligent keys. I've seen ERP software which almost exclusively uses intelligent keys (Navision). Since it works, and is highly adaptable and I don't see any proof in such a statement.

I already know that you can go that way. Indeed, our ERP suite actually still USES intelligent keys in some places. But this is because there are lots of legacy code that we don't have time or resources to change. Is it desirable. NO! Every time we have to mantain that old code, we either change the keys, or had a painful coding journey. We have to use old classes to handle this data that are a lot more complex to use that the newest ones, just because it's not consistent with our new framework.

Indeed, the way you code doesn't implies at all the usefulness of your final product. But when you start thinking in productivity, documentation and flexibility on the long term, things change.