>>Ok, how about a framework routine that automates the task of retrieving a single record by its PK- if some of your tables have multiple keys, you can no longer have a generic function for this. Same with any RI builder and generic Query builder that is deisgned to handle things generically.
>
>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.

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

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

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.

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

You've yet to show one, IMO.

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

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


>As for the corruption. I've got about 100 independent systems up and running all over the country for about one or two years now, and have not yet discovered one instance of corruption of either the table no the indexes. VFP, when using buffering and transactions is far less fragile than FPW 2.x.

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.