Walter,

Let me start this reply with this statement, "There is NO RDBMS available on the market today from any vendor." That said, the comparisons to the degree of implementation of the relational model by various products is moot. Various pproducts do things various ways, none of which affects the relational model at all.

The difference between the definitions of tuples (a collection of attributes) and records a physical storage in a file is not on point here at all.

Deleted recors exist in VFP tables, period. That si the fact to which I refer. One can argue all day long about what SQL Server does, but it is irrelevant when considering VFP tables.

>Agreed 100%. We only seem to have a different interpretation of what a PK is. I don't regard a deleted record to have a PK. I don't regard the existance of deleted record at all. Let's say that the MS team implements my wish to totally ignore deleted records with the use of a table property, the record still exists in the table but you can't reach it, indexes are scoped to non-deleted records the deleted PK value does NOT exist.

If a record exists it must have a PK, in VFP deleted records exist, therfore they require a PK. I don't care what happens in other DBMSs as this discussion is about VFP.

>Further, you can't apply the relational model to VFP. VFP is NOT a relational database. In the relational model deleted records DON'T exist.

Neither is any other DBMS one might consider.

>As for that matter the build in RI builder also does NOT respect the existance of deleted records, because then it would be impossible to delete a parent record at all.

I don't understadn what youa re saying here at all. Deleteing a record does not cause a violation of the PK constraint, adding a new record with teh same value for the PK does.

>This is simply NOT true: the relational model says that any alternate key has an uniqueness constraint. Alternate key's ARE a part of the relational model.

The relational model says that every table will have a PK. A PK is a unique identifier of a specific instance of the entity. The fact that some particular attribute or set of attributes is also uniue has nothing to do with the relation model. The RM does acknowledge that there may be more than one candidate for the role of PK, but the only role the model concerns itself with is the primary key. Any constraint on a non-PK attribute is not a result of the RM but rahter from the business rules for the database domain.

>>>In short. Jim, wouldn't it be much easier to ignore the existance of deleted() records, and all database design rules that apply to other (R)DBMSs would also apply to VFP. If deleted() records did not exist in VFP i'm certain we would not have this discussion at all.

No it would not, simple because they do exist. To ignore them is to work in something other than relaity. Simply because SQL Server does not retain deleted records does not change the fact that VFP does. The records are there, they do exist, to say they don't is foolish. Ignoring deleted records sets up all of the potential problems. Not ignoring them has no problems associated.

To me it is a simple decision that does not warrant the degree of discussion we are having. One) ignore delet4ed records give us filtered indexes which need to be duplicated to provide for optimization, these filtered indexes are not easily maintained through code, the attempt to reuse a PK value causes a potential problem if a deleted record is ever recalled, etc. etc. etc. Two) use a surrogate, there are no problems. Not a difficult decision in my book. Even Codd said, "Intellignet keys aren't!"

>>BTW, I use surrogates in ALL RDBMS tools (SQL Server, Oracle, etc.) and they don't retain Deleted records at all.
>
>So here you don't have the problem of re-using just deleted PK values ! Why should we in VFP ???? The equivalent of a PK in any other (R)DBMS is a PK implemented with primary index with a filter on DELETED() in VFP. You can't change this fact.

No it is NOT. A PK is absolutely unique in all cases. Tye use of surrogates has a special benefit in VFP over the benefits in other tools, it precludes the need to filter the Primary Index. Remember the primary index is a feature of VFP it is not part of RM. The uniqueness of the PK in RM is absolute regardless of the specific implementation tool used. If more than one record exists with the same value of the PK then it is a violation of the RM, period, no discussion. VFP retains deleted records, so having a deleted record and a non-deleted record with the same value for the PK is a violation of the PK constraint, no argument that you or anyoine else can put forth will change this. One can talk all day about how it might be or how it should be, or how they would like it to be, but none of that changes how it is.

>The thing i'm saying is that a VFP PK is implemented with a filtered primary index !! IOW a primary index IS NOT a primary key. A primary index scoped to non-deleted records OTOH is. That this leads to rushmore unusable records is inconvinient but does NOT change the fact.

PKs have absolutely nothing to do with indexes. VFP's primary index in a nice feature of VFP but it has nothing to do with PKs. The lack of a need to filter the primary index in VFP is simply a nice side effect of using surrogate PKs

>>The NF's are 1st, 2nd, 3rd, Boyce-Codd, 4th and 5th for a toal of 6. 4 th and 5th apply to multiattribute PKs. 4th is for PKs with 2 or more attributes and 5th is for 3 or more attributes. If a pk is only one attribute then Boyce-Codd is 5th NF.
>
>Ah.. counting Boyce codd.

Isn't Boyce-Codd an NF?

>>>>>PROBLEM 3.
Walter, with due respect I don't really care about the vacation issue it has nothing to do with PKs. My approach would be to use the dates to find the record, this has nothing to do with whether or not there is a surrogate PK.