Erik,

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

I've been hearing a lot about nightmare, but failed to encounter real nightmires. I'd like to ask in your own words "Examples, please" ?

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

I'm talking about holiday's which apply to the system its in. The PK is a date, if you add a surrogate key, how would you force RI rules ?

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

Then, I think our opinions are not that different at all.

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

I don't agree. This behaviour could be bound to a table property and the central record fetching routine should ignore each deleted record if this tableproperty was set. To be honest, without knowing anything about the internals of VFP, I think this is one of the most easiest to implement.

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

That's personal. I'm not in server (R)DBMS at the moment because I mainly develop standard packages which have to run on minimal costs per seat and minimal administration. I don't think that it's a valid argument to dismiss development on the VFP engine because most of the experts or elite does not use it all that intensive. I'd rather see some figures about the percentage of VFP developers using or not using the VFP engine as the main database engine. The biggest mistake we could make is to let VFP evolve to a product that is mainly suitable for a small fraction of the VFP community. If we want more VFP developers we also must do some enhancements on the VFP database engine !!!

Walter,