Walter,

>It is not my contention that the suggestion above is a good one, only that it is better than pretending the deleted records are not there.

Well, that's where we have different opinions. I do regard deleted() records as non-existing. There is no sufficient control over the difference between a deleted record and a non existing records (deleted record and packed). Therefor IMO the SET DELETED command was invented. IMO the SET DELETED ON Setting is used to denie the existance of deleted records.

I do understand if you consider a the existance of deleted() records, in general you should have no index filter on the primary key.

I say: in general, because I can see other exceptions. In the RM relations are a logical thing. In VFP tables are physical. It's just how do you translate relation into a phisical design. IMO there is room to say that certain records are no part of the relation. This rule certainly applies to deleted records.

I've got some serious problems with your perception that the translation of relations to physical tables is a 1:1 one, because.

- Within VFP some Primary keys (deleted record) do exist, while it's not posible to insert a child record within VFP's current implementation of RI. this violates the RM.
- When you do delete a record, the primary key of that record will exist until packed. Again this violates the RM
- As a user you don't have any control over when a primary key is deleted; they're deleted when the table is packed. Packing tables is certainly not a feature described by the RM.
- Some features, like the DELETE and INSERT update option for views, won't work in your implementation.

These drawbacks are IMO far more severe than having the perception that records marked for deletion do not exist.

Having a solution of surrogate key's is fine for your solution, but according to the RM there should occur no technical problems when I use intelligent or natural keys in any way.

IMO, your own personal solution severely narrows the number of possible implementations of the RM (as much as possible): for arguments see above.

>I never need to take normalization beyond Boyce-Codd because I never have a PK that has more than one field in it.

This could be another topic for discussion. IMO, normalization is a beatifull theory, but when it comes to implementation of database apps, you must not have the goal to have every table normalized.

In vertially none of my applications I use fully normalized tables. There is nothing against redundant data, IF THIS REDUNDANTION IS CONTROLLED. For performance reasons it may be justified to denoermalize tables.

>>According to the RM, Primary keys applies to tuples. If we say that we define a tuple as a non deleted record, there is nothing wrong with my story.

>Mixing metaphores here, a tupkle is a logical design thing and a record is a real world thing. A tuple will, most probably, not have a deleted flag attribute, while when using VFP there is definitely a deleted flag.

Yep,

>>According to your opinion it is not possible to delete any records in a multi-user environment, because the record will still exist ?? This does not make sense to me. In which RDBMS you cannot delete records ?? In all other RDBMS primary keys don't concern deleted tuples, why should it within VFP ? That VFP stores deleted records in a sort of wast bucket is a total different issue. Especially when the available RI mechanism doesn't respect deleted records, why should any primary key ?

>This is flattly untrue, I use surrogate PKs that are always unique. Using a natural PK (which I won't do for a number of reasons) would only entail my first finding any existing deleted record with the PK value and then reusing that one rather than trying to add another new record with the same PK value.

That, in your implementation, within the current RI implementation, it is not possible to insert a foreign key, while the primary key does exists is IMO a clear and severe violation of the RM.

>>This situation exists because deleted records are stored within the table. This is the real problem. If you can decrease the problem by making a filtered primary key, i've got no problem with this.

>My problem is that it makes the primary index not on the primary key.

In what way this is a problem ? Can you point out any PRACTICAL situation that this is a problem ?

>>This proves that again this is a topic for discussion, meaning that others do regard (or want to) candidate keys in RI as a part of the relational model.

>How does "...Note that the Referential Integrity rules requiries the foreign key to match, not just some candidate key, but very specifically the primary key, of the referenced relation..." have any open for discussion interpretation?

Is was refering to: "Again, confusion on this point is not uncommon. Arguments and counterarguments can be found in references". So there are counterarguments, so this is one topic where different opinions exist. I guess thats why it is included within at least some SQL implementations.

>This only proves that we must implement database applications with the features a specific RDBMS offers. We should use the RM as a guideline, but given the limitations, we all can have different perceptions of how we should implement this as close to the RM as possible.

>I will never argue that developers will have differeing opinions about things. I will argue that the RM is used to desing a logical databse and that when one goes to implementation one has to live within the constraints and features of tool being used. However, to consider the tool before the logical design is completed and implementation has begun is a mistake. If one does that, then when teh tool changes the design must be revisited.

I agree, logical database schemes, should be the same for every tool. It's about how do you intent to implement this logical scheme, within the current implementation of the RDBMs.

Within the VFP implementation I don't consider the existance of deleted() records. When i've done that, i've got more room for implementation according to the RM than yours. Again for arguments see above.

Walter,