>Is there any disadvantage to store the invoiceno in a field called inv_pk ? I see your fond of using the word consistency, but IMO its not that simple. Applying consistency where it limits your options might catch you in the end. In general consistency is good, but in the case of this discussion, we should notice that both surrogate keys and intelligent keys do have its advantages and disadvantages (see http://www.bcarter.com/intsurr1.htm). Applying consistency here would automaticly mean that you're not be able to pick best from both worlds. This is specificly important when you want to take every advantage there is.

In your system, is Inv_PK an 4-byte integer like the rest of the PKs in the system, or is your Invoice PK made up of Cus_PK + OrderDate, while Customer_PK is a 10-byte character field, and InvoiceerDetail PK is made up of Cus_PK + Invoice_PK + OrderDate + LineNo_PK, where LineNo is an integer? To me that is harder to deal with.

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?

>>Simple answer: don't use a framework that makes you do this. I don't.
>
>whole tribes do, i'm afraid..

That's how we thin the herd. < g >

>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.

Or create a view of DISTINCT city values. When the user enters a city that does not exist in the view, ask them if they typed is correct. In your scenario, how do you handle a new city being entered?

>VFP does not handle RI natively also, but this is not a valid argument to dismiss it (nor it was in FP 2.x for that matter, though it had to be programmed in screens or PRGs). The point is, that this issue is independend of the (R)DBMS used. Further I don't believe that using surrogate keys would dismiss you from writing triggers to force RI or other business rules to create a well designed database.

We are arguing in circles here.

>Again, I don't see the point of consistency. Let's take the example of cascading deletes. You've got to implement them in a different way for each (R)DBMS. This also applies to some business rules you want to implement in the database. The choice of using surrogate or intelligent keys do not change this fact, and does not matter also as long as the external scheme is the same for each different (R)DBMS.

I am not working just on the external scheme, but on the internal scheme as well. I have made this point more than a few times in my previous posts.

>>Yes, and my point still stands. In your original example, a developer or power user changed the surrogate key in my system. I am saying that with foreign key constraints, which I use, SQL Server will not allow this. So the problem you presented cannot happen in my system.
>
>Your SQL server may not allow this, but AFAIK there are others who do not have this mechanism (VFP).

I setup my SQL Server database to not allow this by using foreign key constraints.

>>I feel like I am increasingly repeating myself. DBMS are different, but I want as much consistency as possible.
>
>See above, though the (R)DBMSs are different the external schemes should be the same. This has nothing to do with the choice between surrogate and intelligent keys. The consistency should lie in the external schemes of the the databases, not in the internal ones. Having two different (R)DBMSs with two different external schemes is not what I call consistency.

My external scheme is much more consistent than yours.

>I don't know if you're getting a step closer: As I replied to Erik, you still have to put some constraints on the intelligent alternate key. Though, you've substituted it with a surrogate key, you'll have to ensure its uniqueness (probably by declaring it as candidate or alternate) and may have to define additional business rules to ensure that for example "an articleno may not change when it occurs in the sales table".

It is not a problem to add unique constraints.

>The point was that in some (R)DBMS you could change the surrogate key without having a RI check for related tables. If not, you'll have to add one, or else the is a potential problem your database to get inconsistent.

Which DBMSs are you talking about?

>Can't you restore a backup on MSDE ? However, I don't think in case of such an event it will be impossible to do so. If the time comes, post a message on the UT and I'm sure some people could help to find the most convinient way to adress this issue.

No, not with SQL Server 7. You can only have one server, SQL Server or MSDE, on one machine. I think this changes with SQL Server 2000, but I am not working with SQL Server 2000. < g >

As far as posting a message, I don't need to. I am not deleting data, you are. But I will be glad to help if you run into this problem.

>INSERT INTO NewTable FROM ;
>SELECT * FROM oldtable WHERE old_pk NOT IN (SELECT new_pk FROM NewTable)

It's a little trickier than that. One, your data is in separate databases, right? Or at least on different machines.

Now, what about the old company, the first "ACME001" that was enter 10 years ago. With your statement above, that customer record does not get restored.

>So how this is not a problem when using surrogate keys ? You'll have to make sure you're alternate key is also unique (after all your user select the customer by its alternate key), I've still have to come accros companies which don't number their customers for other purposes also (e.g. administration, bookkeeping). With surrogate keys the problem might even be worse, because if no uniqueness constraint exists on the alternated key you'll have two customers with the same alternate key without the database giving a sign this happend. How does the user know which one to choose ?

This is your scenario, how easy it is to restore data. You are helping me make my point that it is not, with either solution (intelligent or surrogate keys).

>You've got to restore those customers also. In fact you've got to restore all deleted data which is related to the table to restore or NULLIFY the related column.

See my point above about restoringthe old "ACME001" customer record.

>>Now, just a year ago, we picked up a new customer, Acme Industries. We gave them a CustomerNo of "ACME001". How do you avoid this scenario, and if it happens, what are you going to do?
>
>avoid: Use autonumbering keys to create a new customerno.
>Fix: Replace either the old or the new customer with a new customerno.
>
>Lets leave it with that restoring such backups is never an easy task. In this time of very rappid software development, its likely much has changes in al tiers of your system. You'd likely have to apply a conversion for the old data. Having Surrogate or primary keys do not have anything to do with it.

That was my original point, that restoring backups is not an easy task, so I generally don't delete data. Therefore, I do not <g>generally have to worry about cascading deletes. And with surrogate keys, I don't have to worry about cascading updates.

I think you are making my points for me. < g >