This is one topic that frequently returns on the UT and most people participating in such a thread seem to be convinced to use generated PKs only. I've been thinking hard and long about this topic, especially after my duscussion with JimB, and I still encounter problems in this approach.


PROBLEM 1.
Let's say that we've got an article table wherein the articleno logically is the primary key.

We can implement this in two ways:
1 - make a primary index on articleno and add a filter FOR NOT DELETED()
2 - Add an extra Id field wherein a generated PK is stored.

The vast majority tends to choose for option 2.

WHY ? .......

One of the most frequent arguments tends to be that a PK needs to identify the record and thus should not have a FILTER clause. IMO this is HUMBUG.

If we implement the table by adding an identifier field in which a generated PK is stored, it is Important to note that the Articleno field has become a candidate (or to be more correct an alternate) key because we certainly don't want two (non deleted) records to have the same articleno.

So we should al least implement two different indexes:
- a Primary index on the id field
- a Candidate index on the articleno field with a FOR NOT DELETED() filter.

Additionally to make rushmore do it's job:
- a normal index on articleno.

What we actually did is shifting the problem from the Primary key to the candidate key. Like a primary key, a candidate key should uniquely identify a record. Therefor we are forced to use the FOR NOT DELETED() clause.



PROBLEM 2.

Lets say we have got articles and clients and each client can have custom prices for each article. Then we would make a ArticleClientPrices table with the following structure:

ArticleId, ClientId, CustomPrice

No problem here ! Why should we add another ID field to make it a single column PK instead of a compound PK ?

Pkid, ArticleId, ClientId, CustomPrice

This does not make sense to me. The Pkid probably won't be used in Referential Integrity rules !


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:

From_age, To_age, leave_hours#
    0       21       200
   22       25       210
   26       30       220
   31       37       230
etc..

In fact the From_age to to_age range is the primary key when you want to determine how many leave hours an employee has in a year, based on his age.

Besides having custom made RI rules, how do generated PKs solve this issue ? It simply can't...


MY CONCLUSION.

I don't think that generated PK should be use just because of the problem that VFP still holds deleted records. This problem does NOT exist out of the xBase world and is thus a VFP problem rather than a database design problem. Further there are cases where generated PK's are overkill, and not possible (or very difficult) at all

However in many cases using generated PKs could solve performance problems (because they're smaller) and are a good solution in cases where the logical PK consist of more than 1 or 2 columns, or where logically no PK exists at all.

Performance reasons, however are a doubtfull reason to adjust your database structure: For example: denormalizing your database design is never a good idea UNLESS you're stuck with very problematic performance issues.

...

Any comments ??