Hi chris,

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

So, what do you do when some tables are not replicated, do you use a heterogenous strategy (by using both integer and GUID) or only GUID for consistency ?

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

So do all promoters of the surrogate key. Allmost all do ignore the fact that you can run out of integer values. Get real, how big is the chance that one customer places an order at exactly the same time (1/100 of a second resolution) ?? It is negligible, FYI, a Datatime field infact also is an 32 bit integer field. Also the the PK is a composite key of customerno and datetime. This makes a duplicate key unlikely.

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

Someone else has calculated the chance on a duplicate key. Search the UT for GUID and you'll find.

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

It's definitely more work to adapt your system, and likely to be more buggy.

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

It's about 5-10 minutes of work for a whole system where more than one city fields are used. When using surrogate keys, you'll also have to examine all your programs, views and reports where the city is used. No an attractive solution at all and on top of that tend to be buggy because you're likely to forget some of that modifications.

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

These are additional benefits of the intelligent key. I've also pointed out you can create the data from various tables in which cities occurs. You're avoiding the issue. Is this or not a suitable solution ?

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

I did not change the situation. It was an enhancement or possible enhancement on the situation. If the IT manager gets sick of the remark that certain cities do not exist in the lookup table, he'd probably starts looking for such a solution.

Your avoiding my question.

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

Code from one DBMS is seldom portable to another. If you're trying to reach that, you'll never code in the RDBMS. My standpoint is that if the external scheme says that we should include RI that would mean that I use declarative RI in Oracle and I would program RI with triggers in SQL server. Period. I think we should agree to disagree here.

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

Then you'll be facing problems when your program is used for multiple backends because the external schemes are different it could react different on each DBMS.

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

You've disagreed that the external scheme determines the internal one. YOu've pointed out that the internal one could determine the external. This way you end up with two different external schemes for two different DBMSs. Just reread your previous replies.

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

I think we are lost in confusion.
External scheme: Table (structure), Views, RI, storedprocs, other constraints, etc.
Internal scheme: Indexes, Code (implementation) of views, RI, storedprocs, and constraints

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

So there would be no objection to use intelligent keys of different datatype as long as they are name Tablename+"ID" ? You've just made my point.

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

Where did I say I love triggers ? Sure they're better than handling constraints in your program or avoiding the use of them. If declarative constraints are possible they're better than triggers, which as I stated before tend to contain buggy code.

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

So you're only option is to use triggers. The lack of declarative RI is by no means a reason to omit it, IMO. You're basicly saying if I use SQL server I don't use RI if I use Oracle I'll be using RI. This is what I meant with two different external schemes.

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

So, you must handle this. You've might found a suitable solution by not using cascading RI because SQL supports no declarative RI, but this argument holds no water if your switch backend which does.

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

I see, an order tracking system ! Do you do the same for all other tables, like customer, articles ? And what do you do when a user enters an article "BIKE", cancels it and after that some other users tries to add article "BIKE" again ?

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

Understood, and nicely solved !

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

We don't use an order tracking system. Once delete it's gone.

Walter,