>First of all: Happy new year to you:

And Happy New Year to you...

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

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

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

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

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

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

>See: http://www.bcarter.com/intsurr1.htm

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.

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

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

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

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

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

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.

End Topic One

Begin Topic Two

>>When the head of an organization approaches you after a disaster like this and asks "what could have been done to prevent this?" do you want to have to tell him that you could have avoided it by choosing another backend?
>
>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.

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

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