Chris,

>>Well, what about the case of invoice numbers ??? Are you consistent and add a surrogate key, even though a autonumber invoicenumber would do ? I'm not, because the surrogate key would add no benefit to the current scheme.

>Yes, I would add a surrogate key. The benefit to the current scheme is that I can treat all my tables in a consistent manner. The all have surrogate keys, each is named TableName + "ID". My argument is still the same: consistensy. I'll gladly trade a little overhead, and surrogate keys are very little overhead, for consistency.

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.

>>I do not like be forced to use surrogate keys. Let's say I use a framework that does not allow me to use intelligent keys and I accept a project where I have to work with an existing database containing intelligent keys. How would I solve this ? Saying: I cannot accept this job, because I cannot handle intelligent keys ??
>
>Simple answer: don't use a framework that makes you do this. I don't.

whole tribes do, i'm afraid..

>By adding a surrogate key to one table in my database that does not require it? How is this driving consistency too far?

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.

>There may be, but I think you are missing my point. SQL Server 7 does not handle cascades natively. Oracle apparently does. You have to write triggers to handle cascades in SQL Server 7. You do not in Oracle. I want consistency across my backends. This is one case where I do not get that. With surrogate keys, I do. Simple as that.

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.

>>>Yes, but this specific server is what I am working on. And I want to avoid, if possible, treating it differently than my other backends. Again, I want consistency.

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.

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

>With surrogate keys, I am one step closer to getting that. With intelligent keys, I have to handle the update cascades differently in the DBMS. I believe that Oracle handles the cascade natively, while with SQL Server 7, I have to write triggers.

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

>>There is no way of knowing I the correction was from bad to good or good to bad. As long as the database is consistent the database is correct. You can't protect your database from users entering false data in either case.

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.

>>>Not so easy in SQL Server, at least as far as I know. Do you have any expericence retrieving a backup and integrating it with existing data in SQL Server?
>>
>>Nope, but I can setup a second SQL server, restore the backup and append the old data to the new data. It cannot be that difficult.
>
>I think this is going to be a little more difficult than you think. In SQL Server 7, you can only have one server on a machine. So, to setup a second server, you have to use a different system. And what about liscensing requirements? Are you sure you have a computer around that you can install SQL Server 7.0 on? Does it have enough memory, etc.?

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.

>Now, you may be able to restore the database to a different database on the same server, but you still run into trouble. You can probably use DTS to copy the old data to the new data, but you have to be careful.

As you should always be when playing with data.

>One, you probably do not want to copy old data over new data, right? So, you have to be selective in what you restore. How are you going to do this?

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


>Also, I could not just append records from the the Invoice table. What if the Customer record for an Invoice had also been deleted? You would have to restore the Customer record with the Invoice record. What if when restoring the Customer table, I have a duplicate CustomerNo? This is another example of where your intelligent keys argument has problems.

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 ?

>Let's say that 10 years ago, I had a customer named Acme Technologies with a CustomerNo of "ACME001". We did business with them on and off for a few years, then they went of business. 3 years ago, I backed up my data, then started deleting old customers. ACME001 was deleted.

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.

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

Walter,