>Ah, different than what? AFAIK there no standard software, framework, or guidelines to do this. Also, when dealing with single attribute primary keys whether they are character or integer should not make a significant difference.

No, but I can write more generic code in my framework if I can depend on either all integer PKs or all GUID PKs. To me that is a tremendous benefit.

>I'd like to piont out that there are also 16 bytes GUID (characters) used as a surrogate primary key. These are specifically handy in a distributed database system. Since PKs could be generated by different servers (this is a real potential problem with the enumerating integer PKs). So, my question is, how would you handle that one ? My take on the point is, that you framework should not determine the structure of your database.

I am not sure I understand your question. Either way, I can easily adapt my framework between integer and GUID PKs.

>Wait a minute. The potential problem is so small (a order is entered for the same client at exactly the same time (in a 1/100 of a second resulution) it is regarded negligible. Personally I find it odd that he uses this kind of PK (but maybe wanted to make a certain point here), but thats entirely my opinion and certainly not based on the potential problem.

I really don't see this problem as being neglible. And if he wanted to make a point, he did not do a very good job. He should have explained the potential problem with his solution. He could have pointed out his opinion that the potential problem with negligle, as you believe, but he did not. He failed to mention it at all.

>This potential problem I regard just as high as the change you'll run out of surrogate keys (or creating a duplicate GUID) and you'll have to find gaps and holes between existing PK values. But since when correctly designed and maintained, the potential problems are negligible .

I agree that there is definitely a problem in running out of surrogate keys if you use integers. But we are talking about 2 billion + records here. If that is a problem, use GUIDs. And have not heard of any problems with GUIDs. If you have some information on the possibility of GUIDs not being unique, please let me know.

>>>But this is not an issue either. As long as it is possible to extend this lookup table, there is nothing wrong.
>
>>Walter, the cities example above was your scenario, not mine.
>
>Yes, and ?? Is this not a real world example ??

Yes, it is a real world example. But you have changed the scenario mid-stream. This was a scenario you presented as being a problem for surrogate keys. You have not proven that.

>>This is your scenario, but you have not provided an advantage to using intelligent keys.
>
>IMO, I did, I do not have to change numerous programs to retrieve the city from a table containing an adress nor do I have to change numerous views and numerous table structures. Just add the cities table and adapt my program and/or RI to ensure the user enters a city that occurs in the cities lookup table.

So, you are adding a new table, creating the RI code to make sure each city in the Address table has an entry in the Cities table, and you are changing the UI code to make sure the city the user enters exists in the Cities table.

And this is your proof that using intelligent keys is better?

>>In your original scenario, the Address table already existed and had data, and the request from the manager came later. Now, your are starting with an empty database. Which is it?
>
>I'd buy a table from our PTT containing all cities (and/or postalcode) and/or I'll gather as much cities as I can from the tables containing an adress (could be more than one).

You are ignoring my point. You are changing your scenario mid-stream from an existing table with data to a table with no data.

>>You failed to mention this in your original scenario. And of course, you asked for my solution, and I live in the United States.
>
>You don't have such tables provided by one or more companies ? It could be a hole in the market. Seriously these are all real-world situations where the choice for intelligent/natural keys are very attractive.

Again, you have not proven your case for intelligent keys. You started with an original scenario, I provided an answer, you changed the scenario again, and you still have not provided an advantage.

>When designing and analyzing a database you do this on the external scheme level. When you want to implement it, you still have the choice between different DMBSs. After you choose one specific DBMS you start working on the internal scheme (maybe you've choosen for a heterogenous distributed database system, e.g, SQL - server and Oracle). This is what I call consistenct my friend ;-).

This is completely incorrect. If my product is going to work on multiple backends, I am not going to work in the internal scheme of one DBMS without knowledge of another DBMS. Why in the world would I want to create separate code for each DBMS when I don't have to?

>If in a particular case, you want to take advantage of DBMS specific features, this can be applied on a case by case basis.

Sure, but I am not going to develop my internal scheme separately for each DBMS. Talk about a maintenance nightmare.

>>Again, you are making my point for me! If I want to switch backends with minimal amount of effort, I have to take into account that Oracle and SQL Server 7 handle cascade updates differently. So I can use surrogate keys and minimize the differences in my backends. And I can anticipate SQL Server 2000 all I want, but it isn't going to change the fact that I have to use SQL Server 7 now.
>
>Errr, come again ? You've stated before you want to use different external schemes, which makes it a whole lot more difficult to switch back-end. Also, don't you think that you're using your exceptional case (you don't delete data, use SQL server 7 without RI triggers) as a argument for not synchronizing the external schemes of both backends. This might work for your particular case, but is by no means a standard.

No, this is also incorrect. I do not want different external schemes. Where have I ever posted that? I want my framework to work with both backends as transparently as possible, and I want my internal DBMS schemas the same, if at all possible.

I have never made an argument for no synchronizing external schemes. Just the opposite. While you may consider not deleting data an exceptional case, I do not. It has worked well for me, and avoids all the trouble, which you yourself noted, when restoring backup data.

>>No, you have yet to provide an example of a clear advantage for initelligent keys.
>
>Did you read the messages of Daniel Rouleau. In his example he has a table with 80 foreing keys. If they were surrogate he'll have to unclude 80 join condidtions to make the same report as with intelligent keys. When using surrogate keys, you'll in general need more joins, which makes a significant performance difference. Also note the points I summed up in my discussion with Erik. If they are not significant for you, that's fine, but another might need it to solve an issue by using intelligent/natural keys.

I will look into this.

>>>The main message I wanted to give here is: Use your brains, just think what strategy is best in your case and make a decision based on the advantages and disadvantages you'll find. Not ever, make just a decision based on "for the sake of consistency, I'll always apply surrogate keys".
>
>>There are many advantages to using surrogate keys, including the problem of cascading updates. Yet, you summarize my arguments as just "consistency", as Jim Nelson has tried to do.
>
>O.K. how would you solve the problem of decentral order input. Let's say you've got a undefined number of clients attached to another DMBS which generated surrogate keys of their own. All the orders are replicated to serveral other DBMSs (nodes). In this case you'll be faced with the fact that all those DMBSs should generated unique keys. For these circumstances you might be better of with GUIDs which in fact are character. Consistency ?? Don't you've got to adapt your framework ??

With my schema it is very easy. For example, each surrogate PK is named TableName + "ID". Changing from integers to GUIDs does not effect this. My middle-tier objects all have a superclass method named Load, which accepts a surrogate PK:

LPARAMETERS tuID

THIS.uID = tuID

THIS.REQUERY()

This method is not effected.

My REQUERY method is also generic.

vp_ID = THIS.uID

REQUERY()

Also uneffected to the change.

The only place I have to make the change is in my stored procedure which generates the PK. Very simple.

In your schema, you are over-riding method code, depending on whether or not:

1. You are using a surrogate key
2. You are using an intelligent key
3. You are using a composite key

>>I love your arguments Walter. On one handle, you write that triggers are great when I should use them to handle cascading updates in SQL Server 7. Now, when it does not suit your scenario, they are buggy. Walter, you are being terribly inconsistent.
>
>I'm still not convinced that there are no tools to generated the code for Cascading deletes/Cascading updates/Nullifies and ignore RI options. If they're not there should be some template which describes this to do. Else this is a significant weakness of the SQL server DBMS which in fact does not have anything to do with this discussion.

I have never said there were not tools to do this, just mearly pointing out then when it suits your argument, you like triggers. When it does not, you say they are buggy.

>Triggers might be your only option to implement RI, but they're not the best. Declarative RI is the best option, indeed because they require less coding and they're bugfree (or suppose to).

Yup, but as I have pointed out, SQL Server 7 does not have this. So, it does me no good.

>>You are comparing designing database systems to wearing clothes and driving cars? Now, you have gone off the deep end. < g >
>
>No it isn't. You don't seem to realize that in this world there are different DMBSs, different design strategies, different OSs, different sorts of developers, different opinions, different programming languages etc.

I am perfectly aware of the different DBMSs, etc.. I was the one who pointed out the difference between Oracle and SQL Server 7, which you were unaware of.

>>>The point is that marking data as inactive is not a suitable solution in many cases, and I can't help to 'feel' this isn't the right way. I'd rather take some other actions to maintain possibilities to recover data. You could think of storing older data in other tables or maintaining a backup strategy where recovering is made as easy as possible.
>
>>Why is it not suitable?
>
>You're facing the "history" problem. You want to maintain history for datawarehousing purposes. In fact in general you don't want to delete any historical data. Fine, this I can accept. In dutch there is a lot written by Rene Veldwijk regarding this problem.
>
>However when an order is entered and is canceled any time after that, how would you remove this order, and prevent it beeing recovered when an IT manager orders you to recover history ? How do you handle faulty entered data ??

I don't have to "recover" historical records. They are already in their. Each Order has a status: On Order, Sent, Cancelled, etc. For a faulty order entered, I would mark it as Cancelled. Then the user would be required to enter who cancelled the order, the customer or the user.

This also makes it very easy to resolve customer disputes, such as "Hey, I sent in this Order last week, but we have not received it yet. What is going on?" In my system, the user can say, "Oh, your employee Cindy Hayes, cancelled that order two days ago."

How would you handle it? It sounds as if you have already deleted the order.

>It might be better to find some reference to the 'history' problem.
>
>>Why is this an exception?
>
>I've never heard of anyone (except you) that does not delete data in a database. You're welcome to prove this a common practise in database world.

I have never said it was common, just my opinion.