>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, business rules are separate form the relational model's requirements. Business rules do not have to be part of the data design, they can be relegated to the business logic layer of an application. ONce a PK has been established then teh alternates are not of concern by teh relational model (except for Boyce-Codd Normal Form).

>Candidate indexes can also be used for RI in VFP. Only when you want to make use of the RI builder in VFP, you'll need Primary indexes, otherwise you'll perfectly save with normal indexes where uniqueness is enforced by business rules. But this is another issue.

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

>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. Ignoring them is not a good idea, they are there. One can argue about other RDBMS's all day loing and it does not change the fact. BTW, I use surrogates in ALL RDBMS tools (SQL Server, Oracle, etc.) and they don't retain Deleted records at all.

>In that way, a PK on Articleno would be exactly the same as an unique index Articleno FOR NOT DELETED(). Then we would not have to use these tricks to overcome this limitation.

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

>6 ? As I recall there are only 5. 0th normal form does not have rules.
>In what way this should be a problem ? Anyway Boyce Codd normal form is generally accepted as to be called normalized.

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.

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

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