Erik,

>>I don't see why this is a problem. AFAIK the VFP RI builder does it. You'll have enhance your framework routine, but it is certainly not impossible.

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

>>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. Not forgetting about all the RI rules and possibly all other triggers, constraint, views, replication schemes in there that are broken now. I think you should reconsider your answer.

>>NOTE: you seem to draw the conclusion that using intelligent keys is the same as using composite keys. Let's be clear: They are not.

>If you subscribe to the idea that natural keys are ok, then you will eventually come upon a situation where the natural key is composite (as in George's example). If you are saying that exceptions should be made in these cases, then you've now got a heterogenous strategy, which brings complications all its own.

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

>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've developed an application which database is replicated to and/or serves as a basebase for others. There about 20 of such solution in use right now. I've helped developing an application for the Dutch Waterpolo association which is fully distributed accross many geographical seperated sites. Most competition data is entered distributed (replicated) trough all other nodes in the network by E-mail. You can't accuse me of not having dealt with complications related to this issue.

>> However there are other cases where intelligents keys are a better solution than surrogate keys.

>You've yet to show one, IMO.

Below is one. 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").
>>Well, imagine you've got a table where a date or timefield is unique and that when correctly entered is not (ristricts on child) or not likely (ristricts or cascade on childs) to be changed. There are childs which have foreign keys to this table. There are RI rules between the child and parent. Now add a substitute key to the parent table. Now your RI rules (you can't change a date when a child contains a matching record, IOW you can't change a date of a lesson when a student has subscribed to it) are broken and you'll have to program them in a custom way.

>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. Even if you omit any RI rule, it could stay consistent if you define (depends on the businesrules) that this allowed for the database to be consistent. The real answer: it depends on the business. Some business rules can be implemented by RI rules.

>>When talking about MSDE as alternative to the VFP database, you'll have to admit that there are advantages of the VFP database over MSDE:
>>- No size limit
>>- No user limit
>>- Easy to install (no registration, or addition dlls needed)
>>- Xbase approach that can be many times faster and flexible than SQL

>I'll give you the first three but express that the last one is more a preference than a real advantage.

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.

If your create a hierarchy of more than a few levels, you'll find the xBase construct many, many times faster than the SQL one.

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

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

>Just wait until the first time that the sysadmin installs a new video card on database server, and the server gives a BSOD in the middle of a day of heavy data entry. You'll be singing a completely different song. This is a nightmare even if you have yesterday's backups handy, because the business has to figure out what it entered all that day. When your database costs half of an organization a full day of work recovering from a single crash, I'm willing to bet that the cost of a SQL license will start to sound pretty reasonable to management.

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

Besides this, If, you think geting up your SQL server might be free of pain, think again. It depends on how well and how frequent the SQL server commits it data and/or replicates it to an backup archive. a BSOD could ruin your whole NT instalation (I've seen this happen with the installation of a sound card on NT 4.0).

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. But i guess thats not your point. It's about a BSOD. I guess that in this case the DBF structure sure is more fragile than a SQL database. However, the appropriate steps should be taken to avoid such problems.

Walter,