>>Enhance your routine to handle n parameters? Yeah, I suppose it can be done, but it won't be pretty. I wouldn't want to claim a mess like that.
>
>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.

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

>>>This answer begs the question: What will you do when writing an application for an existing database with composite keys ?
>
>>That's an easy question that I can answer from experience- add surrogates to all tables that don't have them. Problem solved.
>
>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.

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

>>Do you use a framework? Either your own or a commercial one? I can't help but thinking that if you had, we wouldn't be having this conversation.
>
>I use my own. Erik, if there is one person up here who has dived into this issue, its me. Do you really think I would make such statements and not have real world experience in this matter? I've build my own RI builder, Key generation routine, dataform classes etc. I've studied the relation model, graduated on this subject, etc.

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 can't accuse me of not having dealt with complications related to this issue.
>

Again, I didn't. And if it sounded like I was questioning your expertise or experience, I apologize. I only questioned whether you had used a framework for application development.

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

>>IMO, this isn't an RI rule at all, because the imposing factors aren't derived from database integrity, they're derived from real world rules. This is a business rule, not RI, and the only time it will be confused is if you use natural keys. What business does an RI builder have writing code that enforces business rules?
>
>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.

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

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.

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


>>Also, realize that when the first two become an issue, you just purchase a SQL license, and those limitations go away. IMO, any organization that has 20 or more people in the database or is dealing with GBs of data probably can afford a SQL license to buy the security and robustness of SQL...
>
>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. 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? 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.

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

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

>Another point is that anyone who decides to install a graphics card on a database machine on the middle of the day when heavy database transactions occurs is not worth to be called a system administrator at the least.

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.