Jim,

>You knew I would reply didn't you :-)?

Yep, I was hoping so.. ;-)

>>PROBLEM 1.
>>Let's say that we've got an article table wherein the articleno logically is the primary key.
>
>In the case of relational design only the primary key is required to be totally unique. Once a surrogate is used the alternate becomes an issue of business rules and can easily be handled outside of the database in the business rules. It is not necessary to use a candidate index to enforce the uniqueness of the alternate key.

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.

>However, in VFP it IS necessary to use the Primary index for the use of referential integrity as persistent relations MUST originate from a Primary Index.

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.

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.

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.

>>PROBLEM 2.

>As long as no future enhancedment creates a child to this table you are right. However, once a child is born then the compound key is propagated and the trouble begins.

It depends, your choices are:

- Add a generated PK after all (since you'll have to change your database design anyways, this would be not a big problem).
- Use a compound PK for RI.

To be consistent the first one seems to be the most logical step.

>Also, during the normalization process a single attribute PK only requires the first 4 normal forms while a key of two attributes requires 5 and three or more attributes requires all 6 rules be applied.

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.

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

>>PROBLEM 3.
>>
>>In most cases our records are adressed based on a single value identifier. However there are cases where the PK actually is a range of values. Let's take the following example:
>>
>>The number of vacationhours of an employee is determined by it's age. Therefore the following table exists:
>
>Again, the same answert as problem 2. Take this example;
>
>Joe has vacation calculated based on his Startdate. Then the record that was used to do the calculation is changed because the ending date needs to be brought backwards. This causes Joes vacation calc to change. How do you find the original record that was used to calc Joes vacation?

Wherefor do I need the orignal record ? The calculation of vacation hours is on-line; there is no other Foreign key in the persons table than Age (which is in fact a calculation of birthday and the current date())

If you would use a generated Pk then you would need a FK in the persons table. Then you would encounter problems like that the FK is depended on the age of the person --> not normalized and that the FK has to recalculated when the ranges change.

>The date range has changed so it can't be found using the dates, that would find the new record that applies.

If you're asking how to update the vacationhours table, It does not require the PKs to be static, As with normal tables you CAN change PKs (and propogate a cascading update)

>If a surrogate was used then it would be easy as Joes vacation record would ahve the surrogate key for the vacation clac record and it could be found, then once that was done the calc could be reversed and the new record found based on the dates.

Hmmm, I can't seem to understand what you're trying to say here. But let me give the follwong example.

From_age, To_age, leave_hours#

SELECT P.name, v.Leave_hours ;
    FROM Person P LEFT JOIN Vacationhours V ;
          ON BETWEEN(YEAR(DATE()) - YEAR(P.birthday-1) -1, From_age, To_age)

This would give a list of persons with their number of leave hours. (Age = the age of the person on jan 1st of current year).

No matter how and when you'll change the From_age and To_age, the list is correct when there are no overlapping periods (implemented as business rule).

How would you implement this ?

>Also, a major reason (for me) is that consistency is more important than saving space (unless space becomes a demonstrated problem).


Yes, in general consistency is good, but what are the rules? I could have a rule that I only use generated PKs when there are childs (FK) or for a replacement of a long compound PK. I could be as consistent as you do.

>Therefore if one table has a surrogate key then I want all tables to have a surrogate key. This removes the need to keep track of which tables have surrogates and which do not. It also means that NO Primary index has a filter on deleted and it prevents the need for a second index on the PK for the use of Rushmore (it won't use a filterd index).

But you will need a buisiness rule to enforce the intelligent key (Articleno) to be unique, which IMO is best implemented with a filtered candidate index.

Walter,