Hilmar:

> I use a primary the which the user never sees. This key is integer, and
> "auto-generated".
>
> Main advantages:
>
> Only 4 bytes in main table - hardly an advantage, since I need the SSN anyway.

This is always a disadvantage because the surrogate is an extra field. The 4 bytes statement is not applicable to a VFP backend (unless you define your PK to be N(4,0)) because VFP requires n-bytes to store an N(n, d) field in a table.

> Only 4 bytes for reference in other tables.
In addition to the previous comment, a lot of lookup tables can have very short (1-2 bytes) keys. The comparative size of the surrogate key may or may not be an advantage.

> In all cases, a single field can be used for Primary key or Foreign Key.
I agree. I tend to stay away from compound PKs as a general rule, especially if the key is to be used in a relation. However, most natural keys are also made up of a single field and thus, this advantage only applies to situations where you are considering a surrogate key vs a compound natural key.

> This fact simplifies relations.
I agree but see previous comment.

> The short (and single-field) key makes joins faster.
I agree with you when using short integer keys (which is what you are doing). However, some developers (I am one of them) use GUIDs instead of auto-incrementing sequences for reasons that are beyond the scope of this discussion. These GUIDs actually make joins slower.

The point that Walter, JimN, and I have been trying to make is that you have to evaluate the pros and cons of using surrogate/natural keys for each indiviudual table. Surrogate keys are not the mythical silver bullets.

Daniel