>The PK is a 32 bit integer field. The only difference between the surrogate and intelligent key here is that the in this case the inv_pk has a meaning outside the RDBMS.

Yes, in this example you can use an integer field, but you are sidestepping my point: what happens when you have character PKs and integer PKs?

>If handling composite key is an issue in your application, you could choose to replace it with a surrogate key.

My problem here, I always use surrogates.

>>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?
>
>The chance that it would happen is, very, very small, since the datetime datatype has a resolution of one second or 1/100 of a second. If accendently (once in a very long time) it will happen, the primary key uniqueness constrain will let the insert fail. Wether you do or not do handle this situation, the database remains consistant. OTOH, I've got to agree that this solution seems a bit odd to me. I would have choosen for an internal orderno, generated by the system in the same way as surrogate keys.

It doesn't give me a lot of faith in the rest of what the author writes.

>>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?
>
>There is a difference in the two approuches: The DISTINCT approach would only recognize cities that are already used in the adress table. The lookup table would allow you to enter all cities at forehand (I know this is a hell lot of more work in the USA than in my small country) and does not allow the users to enter a city that does not occur in the lookup table.

Are you saying that cities in your country have been fixed for a very long time? No new ones, or the potential for new ones? Either way, I would think that having all cities entered beforehand is questionable.

>IMO, this situation is not an academic one, since our PPT delivers a postal code table (updated regulary) in which the postalcode automaticly defines the Street and City. In this table the postalcode is the primary key. This allows your application to always have the right adress in your table. If not other PK is available than the postalcode it is harder to deal with surrogate keys here.

Your argument is very confusing. In your previous post, you introduce a second table with the just the cities as your solution. Now, it appears as though selecting DISTINCT cities will prevent an incorrect city from being entered by the user. Exactly what are you trying to say?

>>We are arguing in circles here.
>
>Yeah, I think so, we seem not to able to catch eachother point.

>The point I'm trying to make is that, I don't care about the internal scheme: If you've got to do more work to implement cascading deletes and/or updates, so be it. The point is that the external schemes of two the same databases in two different DBMSs should be the same. How you handle this in the internal scheme is not a valuable issue whether to use one over the other strategy.

I could not disagree more. If I am the one doing both the internal and external work, the two are very related.

>No, the external schemes (Tables, fields, datatypes, RI rules and other constraints, SQL) should be exacly the same no matter which DMBS used. (Yes, I know that certain DBMS do not support certain data types and there are differences in the SQL language, but at least try to be as uniform as possible).

Ah, so you are arguing for consistency?

>And what about the the "an articleno may not change when it occurs in the sales table" (restrict rule). When the articleno is not the PK anymore (since you've added an alternate key) you can't use an RI rule since the Articleno does not occur in the sales table anymore. You'll have to write a trigger for that.

Not a problem. Either way, I can write basically the same trigger for my multiple back-ends. Consistency.

>>>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?
>
>At least for VFP, I do not know for other DBMSs for sure. Maybe someone else could bring up one or two more.
>
>>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 >
>
>What's a problem by using a second machine, when handling such exceptional cases ?

Not a problem, so long as my client has an extra machine and a license for SQL Server. How many clients do you have that have an unused machine lying around with the specs to run SQL Server, and an extra license for SQL Server?

>>It's a little trickier than that. One, your data is in separate databases, right? Or at least on different machines.
>
>My preferred strategy would be to gather all the needed data in VFP and export it to the new database.

Your scenario is becoming more time consuming as this discussion goes on. One, your backup data is in SQL Server, so you have to restore it. I have covered the hurdles with this. Then you have to create a VFP program to access it. Then you have to decide what you bring in from the backup, making sure not to overwrite existing records, and making sure when you bring in an old Order record, you also bring in any parent records. Or, you could have just marked the data as inactive in the first place and avoid all this.

>>See my point above about restoringthe old "ACME001" customer record.
>
>As stated above this still is a problem with surrogate keys.

The original part of this discussion related to deleting and restoring data, not the intelligent key vs. surrogate key debate.

>>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 >
>
>I don't know if I do, and for which argument. I still find it odd that you don't delete data in a database (and i think most of us would agree). I think that there are other alternatives to this issue. I would have a hard time to imagine one case where I would not want to delete data in a whole database.

Let's just agree to disagree.