Hello, Walter, Chris.

I finally found the reply you cited, Walter, about the citis issues, but first...

>I read the paper you cited, and right off the bat I have a question:
>
>"A child table called order might have an intelligent primary key consisting of two columns: customer_number to act as a foreign key pointer to the customer table plus order_date_time to identify different orders for one customer."
>
>What do you do when you get two orders from the same customer at the same time. It is not going to happen very often, but when it does, what do you do?

I don't read the paper, but that statement alone depict the writer as a VERY poor designer/developer. I can understand using an intelligent key, but not a stupid one. Counting in how many times some thing could happen is a sure road to failure.

Let's go to the city issue:

>>Well, O.K. another example. lets say I've got a table with adresses in it:

>>Adress, city, postalcode, zipcode, etc,

You've build this system and after a while the IT manager comes to you and says "Chris, we've got some major problem here. In some cases the users make a typo in the city field. We'd like the to program check if a city exists". What do you do ?
>>
>>A. Add a lookuptable consisting out of one field: "City", mark it as primary key and mark the field city in the adress table as foreing key and apply RI rules ? Since the structure of the adress table does not change there will be no programs and views end up broken.
>>
>>B. You replace the field city with the field city_ID, add a lookuptable with two fields (city_id and City) ? This could cause numerous programs, forms and views to be checked and fixed to handle the new situation.
>>
>>C. Another solution.
>>
>>Well, I know what I would choose, I don't like to spend hours and hours to adapt the program to the new situation when not neccesary at all.

I will follow up the tale:

Suposse that our godd friend Chris ffollows your advice (this is just fiction, you know) and picks option A. Next week, the ever-smiling IT manager comes and tells him, "Hey, Chris, we have to put some table so our delivery people can store shipping costs for each city and customer type".

No, problem, thinks Chris. I just need to add a shipping cost ffield to the new city table and... But, wait! It doesn't work, as I need a different cost for each customer type, so I have to create a many to many table.

Of course, Chris can also put three different fields in the city table, as there are just three customer types, and this will NEVER change.


Get my point? Intelligent keys are not good for unexpected changes. Surrogates -by definition- are. You could save yourself a bunch of lines of code now for not using it, but it can get you stuck with a big rewrite afterward.

As I've already told, I have used intelligent key myself for years. I have software pieces that still uses it because I haven't had the time to change it. As far as my expirience goes, I had LOTS and LOTS of maintenance and extension troubles that disapeared when I switched to a surrogate-only style (which, alas! is not complete yet).

Bye!