>Erik,
>
>>> One question though. If you look at your projects, do you have a surrogate key for all (I said all tables intermediate results and cursor results included) ? I'll bet you don't. Then ask yourself why didn't you apply a surrogate key in this table. You may find one of the reasons i've mentioned before.
>
>>The only tables that do not have surrogate keys are those that don't get updated by the user, and that are not involved in any relations. IE, lookups. As soon as the need arises to do either of these things, the table gets a surrogate PK. In _all_ cases.
>
>From this I draw the conclusion that you don't either support the Forced use of surrogate keys. You've clearly drawed the borderlines where you don't want to use them (because they're not neccesary, just like a table containing hollydays ; the PK is a date). You've showed that at least you've though about this. The small difference between your and my standpoint is that I do allow intelligent keys in relationships. And when they change, just do a cascading update.
>

Ok, I should have stated that the only tables that do not use surrogate keys are the ones that don't really need a PK for anything but conceptual purposes. All tables that have a PK use surrogate keys. The practice of using natural keys also leads to the natural need to use a compound keys (for many tables, the natural key is the combination of several fields), which at the very least leads to inconsistent key strategies for different tables, and at the worst creates all sorts of nightmarish problems in queries and updates.

(BTW- Your example of a holidays table isn't really a good one for this, because there are different holidays for different companies/ countries, and they are likely to change- the list of days in a week or months in a year, however, is not likely to change)

>Again I don't think so. The questions here often also apply to other (R)DBMSs like SQL-server and Oracle. Besides, I believe that most practices regarding handling data is transparent trough all (R)DBMSs. It should not make much of a difference, if you're using VFP, SQL-server, JET, Oracle, etc when we are are talking about SQL.

While I happen to believe that the use of surrogate keys is wise in any RDBMS, I don't think that I have ever instructed anyone that this is the only way to go in all systems.

>Comming to the VFP topic. Did you never wonder how we could get rid of the status of deleted records ? This issue IMO is far more important that debating about advantages and disadvantages of intelligent and surrogate keys. I've submitted a wish that it should be possible that when a record is deleted, all xBase commands are scoped to non-deleted records only (even an INDEX command, and regardless of the SET DELETE setting). IOW when a record is deleted, it's gone (permanently hidden until a pack, or changing a table property). When this is implemented there is no difference between VFP and other (R)DBMSs when it comes to violation of uniqueness of PKs.

I think that's a valid if not practical wish, but I wouldn't expect to see it implemented. Deleted records have been an xbase way of life since the beginning, and making a change like this would break too much code designed around this long standing fact of life.

FWIW, the more I use server databases like SQL and MSDE, the more put out I get with the quirks inherent in VFP data. The Deleted records issue, the corruption issue, security and all that just make VFP tables look less and less attractive especially now that the SQL engine gives us comparable performance.