Erik,

>>So, what do you answer if someone asks, I've got a uniquness of primary key violated error, what do i do about it ? Do you anwer, don't use intellikent keys, use surrogate keys ???

>I first ask "who designed this database? Walter?", and then "can we get rid of your intellignet keys?" If not, I'll make do. I'm a pretty resourceful guy.

O.K. I can live with such answer. But the only correct answer should be IMO: "Filter the primary indexes on DELETED()". This is the direct cause of the problem and only exist in xBase databases.

Hmmm, its not likely you would encounter a database designed by me. Besides, a lot of my tables do use a surrogate key. In case where an intelligent key is used they do not form any problems (and generally won't contain composite primary keys, only composite alternate ones).

>>1. Surrogate keys in general do require more joins in a SQL statement.

>Not if you resign to the fact that composite keys follow intelligent keys. Then you have _more_ joins.

Please, stay awake Erik, this is incorrect. The join expression is longer, but there are less joins needed. In terms of performance this is a big difference.

>>2. It adds another column that in a significant amount of cases is not neccesary.

>Not an issue, IMO. And not nearly as significant as the additional columns required when using composite keys.

I don't think composite primary keys should occur that often. Besides they can be avoided by using an alternate (can be both an intelligent or surrogate key). Note that even if it is used as primary key, the overhead only applies to child tables. I really like to see some figures what percentage of intelligent keys are composite keys.

>>3. People tend to 'forget' to mark the alternate intelligent key as unique, which could cause problems of its own. Checking for uniqueness in code is far less reliable. When using a candidate index, you'll still have filter for !DELETED()
>
>Straw man. Stating that walking is better than driving because people sometimes drive drunk is not a valid argument.

Well look at the already described issue: "I've got a uniquness of primary key violated error, what do i do about it ?". Most regular answer: "Don't use intelligent keys, just use surrogate keys" Look at the Article table example.

If you add a surrogate key (art_pk), you'll still have to make sure the intelligent alternate key (articleno) remains unique. My standpoint is that this can only be done effectively by using a filtered candidate key (on !DELETED()).

Look at what happend. You've added a surrogate key (Art_pk) and still have to add an extra candidate index (On articleno) to force uniqueness. If you don't filter this index on deleted(), you did not solve the problem at all, you'll still get the same error when trying to recycle the articleno.

Conclusion, the answer: "Don't use intelligent keys, just use surrogate keys" to the question "I've got a uniquness of primary key violated error, what do i do about it ?" cannot be correct, it simply cannot: it does not solve the problem at all.

From this I can only conclude that whole tribes do not add the uniqueness constraint on the intelligent alternate key.

>>4. Business rules that could be handled with RI in case of intelligent keys, need a custom business rule (implemented by a trigger) in case of an additional surrogate key.

>I've already stated my position on this- I don't think an RI builder has any place writing business rules.

So the decision wheter to use a cascading delete or a restrict RI rules is not based on business rules ?? If not, on what bases do you choose ?

>>5. Using a thrid party query tool for end-users is generally easier when using natural keys. Users can easely see what a column means.

>A third party query tool usually has the table table relationships preprogrammed, so the user just picks fields and leaves joins up to the program. I have 3 live applications that use FoxFire! as the reporting tool, and allow users to make ad-hoc queries, and the surrogate key issue is not an issue.

FoxFire might do so for VFP data, but does it do this for any other RDBMS? I don't have the impression that reporting tools do draw relations that automaticly. Often they only propose relations on field between two tables that have the same name.

>Ok, nothing new. Notice the couple of quotes where he states that intelligent keys can lead to more columns in the table and more clauses in the SELECT statement. Given, these are sometimes advantages and sometimes disadvantages, so in the very best case for you, your points concerning this are probably a wash.

So we can conclude that there are advantages and disadvantages to each approach. So would it not be wise to at least consider which to use in some particular cases ?

>>You've got to distinguish natural and composite keys. They're not the same and only faintly related. This discussion is about Intelligents versus Surrogate keys and more importantly the forced use of both of them. I've already stated that I use surrogate and intelligent keys on a case by case basis.

>I refuse to separate the two, because IMO, one follows the other. And I have already stated the issues I have with using a heterogenous PK strategy.

I failed to have catch your problems with a heterogenous strategy. If not using composite keys, Tell me what are your objections ?

>>When using surrogate keys, you've got to have one RI rule (restrict) to prevent the change of the surrogate key, and you've got to write a custom businessrule to prevent the change of the data of a lesson when a student has subscribed to it.

>You can also avoid buying a scooper by using your hat to pick up after your dog.

eh... I'm not sure what you're saying here. I'm merely saying here that in this case with intelligent keys, you'll need only one RI rule. With surrogate keys, you'll need one RI rule (restrict for the PK) and a business rule (to prevent the change of the date of a lesson when a student has subscribed to it).

>>>Logically, this is about the same number of steps. Performance-wise, I don't know because I haven't tested, but even in the largest companies, I can't see this having to iterate more than 15 or so steps, so performance shouldn't be much of an issue.

>>This issue is not about the number of iterations but about the principle. It's not that difficult to imagine constructs that can have hundreds or thousands of steps. Both performance and programming efficiency is worse in the SQL case. Try migrating this example to SQL server. You asked me to prove a case where xBase was performing better and is more flexible. I just did.

>No, you didn't. You just restated that xBase is faster.

From looking at the code, you don't have to be an expert to see that the xBase code is far faster and far more efficent than the SQL one. Conclusing in this case it is more flexible.

>>Personally I've got a case (calculating a roster) where there are about 15 tables involved. I've Optimized the routine, so it takes about 0.02 Seconds to calculate the numbers of different kinds of leavehours, hours which can be taken to next year, and a whole bunch of other data.

>>Without the flexibility of xBase it would have taken 10's or even 100's times more time to accomplish the same in SQL. When wanting to print about 1000 rosters for different people this amount of time is very, very important.

>So? I've got lots of code that does seemingly extremely complex tasks in seemingly tiny time periods. This proves nothing.

This is an easy statement, I'll bet that when using xBase Seeks, buffering strategies, cursors, relations, set near on statments you can even optimize those routines. The fact is that in xBase you've got more control over the internals of the database than with SQL.

>>How would you prevent the Articleno from changing if this article occurs in the sales table ??

>>Answer: You've got to define another additional businessrule to take care of that one.

>As I stated above, because I don't feel that business rules belong in the database, I don't see this as an advantage.

>Walter, given the length of this discussion and the number of times the same points have been repeatedly raised, I think that we are going to have to agree to disagree here.

I'll wait until your answer, after that we can draw a conclusion; it's far more productive than just saying we agree to disagree.

>But the whole reason that I engaged you on this topic is to attempt to justify ignoring your viewpoint when I advise people on a PK strategy. You tend to think that if someone recommends a surrogate key strategy, it is because they are following someone blindly, or because they don't like Walter. On the contrary, I have chosen my strategy after much careful thought on the issue, and after having to learn the hard way (I began all of my professional work assigning an intelligent key to every table, and had to learn from experience why this was bad). I will continue to offer my preferred strategy to those that ask, and I will continue to neglect to mention your preferred strategy in these
>cases, because I think that your preferred strategy is wierd, and as you have probably noticed, you seem to be alone in the xbase community with regard to your PK convictions.

No, i'm not as I've encountered some people with the same standpoint as I have. The only conclusion I can draw is that I disagree with the people answering to these messages. This is a whole lot different than the whole xBase community, and this was also the point I was trying to make. I'm certainly convinced that there are people who are afraid to express their standpoint (we've seen prove to that). With these discussions Im trying to create an environment where more people with different voices get involved. This would lead to more alternative standpoints and a wider view. Hereby I would like to thank you for your patience to engage in this discussion. I like to express that in these discussions there are no winners or losers. We all win from such discussion because it provides people information on which other developer could draw their own conclusion. IMO, this is far more effective than buying and reading a book which promotes a certain development strategy (like the use of intelligent vs surrogate keys).

>End Topic One

>Begin Topic Two

>>For each project when appropriate I tell the client what its options are. If choosen for VFPs engine, this is in agreement with the IT managers.
>
>There comes a point when you _are_ the IT manager, and you are answering to an executive. Or when you are independent, the client often relies solely on you for decision making. Stating that somebody else knew about the decision ahead of time really takes no weight off of your shoulders.

In this case, I'll gather all the info needed to make a good decision. If after all it was a bad conclusion, I know the decision was made by looking at every aspect and could not accuse myself for making a bad decision. Note, that all decisions can be bad, not only the choice for a xBase database.

>>For smaller businesses where the role of a system is not that significant, a server RDBMS is not likely to pay off ever.

>It will if the business is small enough for MSDE.

It will likely take more time to develop an application for MSDE (at least for me) than just xBase tables. Therefore it is not worth the costs.

>>This situation would never occur in my situation. This topic would be discussed before I start working on a project.

>It _was_ discussed. The IT manager asked my opinion about the right backend, and I told him my opinion was VFP. He agreed, so we proceeded with VFP. But that doesn't change the fact that many dozens of man-hours of work were lost partially because of the choice of database.

It's not that difficult to imagine cases, where problems with the SQL server occurs. The case is you'll never know unless you've build both the solutions and use them simultaniously.

Walter,