Hi Erik,

First of all: Happy new year to you:

>>Who said anything about parameters ? All the PK info is in the table itself. I don't see where it would be neccesary to drive this into multiple paramters.

>If you have a business object class that has to retreive a single record from a table by its PK, and your table has a composite key, that GetByPK routine has to handle multiple parameters. With the consistent use of surrogate keys, the routine would need only a single parameter, and would never have to change.

I see... Of course there are alternatives (passing the primary index expression, or passing an optional array), but I've got to agree that in this case defining another PK would be easier (though I won't call this a nightmare). Note that it won't have to be a surrogate key. If you look at the case written by George Tasker, I can imagine that the 'surrogate' key used is printed on the back of the carpet (isn't this an ISO 9002 requitement ?). Though this key is generated (see my replies to Chris M), it is intelligent as it has a meaning outside the database (just like a generated invoicenumber would).

>Can you explain how your framework generically handles composite keys in its data/form classes?

By its primarykey expression. In fact it is only used to force uniqueness. In views, I use the posibility to use more than one keyfield. My framework does not handle SPT (yet), since the lack of SQL server based projects. When I do, I'll have to make a choice: Use non-composite keys only, or find a convinient alternative.

In general I don't like restrictions (imagine I've to write an application for an existing database, also used by other applications. I'll have to find an alternative anyways), I'm trying to consider all situations. The last thing I want is to limit the application for my framework, or create narrow minded solutions.

>>Then, you've never done this for an database which was used for other purposes also. There are a lot of complications that could arise when you do so. If other client applications are attached to it, you'll have to change them also.

>So your question is "what if a database uses natural keys and you can do nothing about it?" Well, my answer is "I can do nothing about it". I don't understand the significance of any answer to this scenario.

Then you'll have to use intelligent keys, whether you like it or not. It sure comes in handy when you've dealth with it in your framework and have considerable experience in this field. 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 ???

>>Yes, indeed, you've hit the hammer on the nail! I do use a hetrogenous strategy: Decide which sort of key to use (surrogate or intelligent) on a case by case basis. However I don't fully reject the other two strategies as well. I do reject the claim the using surrogate only because "the two other will only cause problems". Let's not be a doubt about this: "All strategies have its advantages and disadvantages".

>Again- despite your repeated efforts, I have yet to see any advantage to natural keys, or any disadvantage to surrogate keys.

1. Surrogate keys in general do require more joins in a SQL statement.
2. It adds another column that in a significant amount of cases is not neccesary.
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()
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.
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.

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

>I didn't question how much experience you have or how many books you've read, I asked if you've used a framework. Have a look at any of the commercial frameworks available, and attempt to use one of them, and tell it that your tables use natural/composite keys. Won't work. And it's not because the frameworks are poorly written or inflexible, it's because framework development in its very nature relies on a consistent PK strategy. An inconsistent PK strategy necessitates inconsistent development patterns, which make for an app that's a PITA to maintain. The fact that _you_ are used to maintaining apps like this has little bearing on another developer that has to look at your work down the road.

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.

>>And if you're willing to read Joe Celko, you'll find plenty of others (I believe he had written an atricle "17 reason to use natural keys").
>
>I would be interested in reading this article. Where would I find it?

AFAIK it is not available online. I've read it somewhere a few years ago, but I don't seem to recall where. But if you can get search for Celko, you'll find some other interesting articles and books.

>>This is incorrect. The choice between cascading and restrict RI rules are almost alway's related to business rules. Tell me why in some cases you would choose for a restrict rule and in others for a cascading rule ????? for database integrity ?, not at all: database integrity is maintained in both.

>Again, this only gets confusing if you use natural keys. For me, I _never_ use Cascading updates, because I never need it- my PK doesn't change. For deletes, I either restrict or cascade, depending on the situation. Failing to do either results in child records with invalid FKs. An invalid FK is not a business issue, it's a database integrity issue. I still can't see a situation where the distinction is cloudy, except with natural keys.

Its about remaining the integrity of the data. If using natural keys in RI, You both achieve the database to be consistend and the businessrule which says "A date for a lesson cannot change if a student has subscribed to that lesson.

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.

>>I can constuct xBase DML routines that have NO suitable equivalent in SQL. For one, Try recursive constructs: An emplyee has a boss, a boss itself is a employee and has a boss.
>>
>>Now create a routine that retrieves the most upperboss for a given employee.
>>In xBase:
>>

DO WHILE KEYMATCH (Emplyee.Bossno, 1, "employee") AND Employee.Bossno # Employee.empno
>>   SEEK Employee.Bossno ORDER TAG Empno IN Employee
>>ENDDO

>>Please add yours in SQL.
>
>

>lnBossID = 11
>DO WHILE .T.
>   SELECT BossNo ;
>      FROM Employee ;
>      WHERE EmpNo = lnBossID ;
>      AND EmpNo <> BossNo
>      INTO CURSOR TheBoss
>   IF _TALLY = 0
>      EXIT
>   ELSE
>      lnBossID = TheBoss.BossNo
>   ENDIF
>ENDDO
>

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

>I've written this in VFP, but the same could be done in T-SQL. I don't think it's worth your while to cite the performance difference in this scenario as enough of a reason to choose any one DBMS over another.

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.

>>When replacing the intelligent key "Empno" with a surrogate key. Write the proper rules to prevent to enter a bossno that does not exist. (If you don't regard this to be a RI rule, at least notice that this looks a hell like a lot of to be one).

>This is an RI rule, because you have defined a unary relationship, and BossNo serves as exactly the foreign key. I don't see the difference here between BossNo being surrogate or natural, if one can use an RI builder to enforce integrity, the other one can.

I agree, this example is not representive. Lets look at the following:

A. Table: Articleno, Description, price, etc.

The articleno is an intelligent key and has RI ristrictions with child tables (the articleno may not change if it occurs in the sales table). Since you'd like to use surrogate keys you'll add one:

>B. Table: Art_pk, Articleno, Description, Price, etc.

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.

>>But if they've got to choose between cost nothing and costs $25.000 and bet I'll now what they're going for. It's not what they can afford, it's what is going to be the cheapest in the end.
>
>Cheapest in the end is exactly my point. See my scenario below for how a database prone to corruption can cost an organization much more than the license cost of SQL Server.

O.K. give me mathimatical model to calculate this. There is not ! No-one knows how many times suchs an event could occur. Even then it is the question if the a SQL server could prevent these costs. IT managers will take this issue into account, but when addtional problems are not likely to encounter this $25.000, I'll bet I know what they'll choose.

>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. Of course much relies on the nature of the business. If it is a highly dynamic online transaction system (OLTP), I agree, we sure are better of with a server (R)DBMS. But if we are dealing with mainly a decision support (DS) system in which data does not change that frequently, xBase can be an appropriate choice.

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

>I was ashamed to tell him that the other backend would have only cost his organization a couple of thousand dollars. He would have been glad to pay it to keep his business on line.

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

>>For my systems, this won't be much of a problem, because the amount of data is not that high per day. Since I've got some backup-routine build in to the software they can make online backups (posted a few weeks ago in some thread about making online backups). Even if this an important factor I could build in an transactionlog (For one project, i've already done this), to overcome crashes in very much the same way as any server DBMS.

>You can build as much preventative infrastructure as you want, but you'll never approach the built in security of SQL server, and the point comes when you have to ask yourself if the price difference has been worth all of your effort?

The decission is not made by me, but by the IT management. All aspects which play a role in such system should be considered. If security is a toppriority (banking) then sure it might be wise to choose for a SQL server.

>> Then again, much depends on the configuration of the server (there are some registry keys which decrease the risc), the number of users in the system at the moment of the crash, the current transaction, etc.

>This is true, but none of these measures is comprehensive.

Nor will be any steps to avoid the problem with SQL server; In general there is no way to ensure that made transactions are recoverable: Commited transaction tend not to be written to the disk or archive inmediately.

>The video card was installed the night before, and only caused a BSOD when one user on another machine called a print job on one printer on another server. Sounds wierd, but the problem was reproducible. The point is, you can't foresee machine problems like this.

>> However, the appropriate steps should be taken to avoid such problems.
>You can't Walter, that's my point.

I said, AVOID, not prevent. You never can prevent such problems, not in SQL server nor with a xBase approach. You'll have to avoid such problems when you can.

Walter,