Jim,

>>This seems an odd step to me. In the logical data design, it is defined as a primary key.
>>- In the physical datadesign it isn't even an alternate key ?
>>- Then, How do you enforce the uniqueness of the articleno field ?
>>- How would you define this in another (R)DBMS, where the existance of deleted() records do not exist.
>
>The RDBMS is irrelevant, PKs are required to be unique by the relational model.

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.

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.

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.

>ONce a PK has been established then teh alternates are not of concern by teh relational model (except for Boyce-Codd Normal Form).

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.

>Candidate indexes cannot be used to define persistent relationships in a dbc.

It can, Just try it !!

>>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, deleted records exist in VFP tables, period.

Deleted records are not tuples, The relational model handles tuples, not records.

>Ignoring them is not a good idea, they are there.

Untill the table is packed, which is certainly not an action that has any counterparts in any other relational DBMS, which cannot be triggered, and which is only implemented for technical reasons.

>One can argue about other RDBMS's all day loing and it does not change the fact.

Which fact ?

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

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.

Let's say we practise downsizing. We have a database design that does not use generated keys for all tables. In the upsized version you can recycly PK value in the downsized VFP version you can't. How do you explain this to a NON-VFP programmer ? Are you telling him a story about deleted records in VFP ? I'll bet he thinks you're nuts !! The only solution you've got is to use filtered primary indexes to implement primary keys.

>Using a business logic enforcement layer is not a trick, it is the N-Tier design.

Ah. I assume that you would implement this in a business logic layer, whether or not as stored procedures, and not with the help of alternate keys.

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

>>>Using the surrogate makes normalization an easier process and allows for easier future expansion of the database.
>>
>>I can't see this. Can you give an example ?
>
>It eliminates the need for 4th and 5th NF to be applied.

Do you imply this such a difficult task, that this would justify the forced use of generated PKs ? It seems to me as using a gun to kill the fly that bugs me.


>>>>PROBLEM 3.
>
>One can take any example and get the results they want. In my opinion it is better that ALL tables have a surrogate PK than that I choose to use one or not based on what my current idea of the table's use is. I have, all too often, seen the use of a table change later in the development process. So, I use a surrogate PK for every table and I don't think about it any more. I have NEVER encountered any situation where the presence of a surrogate PK caused any problem at all, yet I have often encountered problems regarding the use of natural keys. So if I have one method that sometimes has problems and an alternative that NEVER has problems, I choose the alternative.


You didn't answer my question about the vacation hours. How would you solve this problem with the use of generated PKs ??

Jim, I agree that problems 1 and 2 are not heavy. On the other hand i'm not convinced that you can solve problem 3 just as easy as with intelligent keys.

Walter,