My dear Walter,

>>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.
>
>The database schema is not a problem at all. It generally takes just a few minutes to do this.

You are not reading, I'm afraid. When the key becomes bloated, beside the changes to the scheme, the data have to be reprocessed (in most cases), and the underlying code to handle the added fields or different size have to be propagated trough ALL your application. That DOESN'T happen with 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.
>
>But it is the most easy one to implement. With this intelligent key solution I don't have to manage the complexity that comes with surrogate keys. (note that this same argument is used in favour of surrogate keys in other cases).

I have replied this in a previous post where I find your example. Yout turn to search for mine. 8-)


>>>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.
>
>Navison Financials, is a Windows based product only about 3 or 4 years old. Legacy code does not exist here.

Good to know it, just in case I get an offer to work with them.


>>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.
>
>Hmmm, I see a heterogenous environment comming up !!!

Of course it is heterogenous! That's waht I'm saying. Mantaining the parts not using surrogates are PAINFUL. But the whole suite involves around 35 modules (different projects), with and average of 20/30 forms each, plus classes, code, etc. I would be VERY happy being able to eliminate intelligent keys, but I think some would remain there maybe another year or so.


>>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.
>
>Depends on the case.

It doesn't depends. Giving the same functionality, scope, performance, and the likes, any system designed and implemented in a consistent way it would be far more easier to document, mantain and extend. Want me to sign it? 8-)