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

I use integers, which use 4 bytes, but can store numbers up to 2e9. Am I missing something here?

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

That's right. I use surrogate keys for several other reasons (size is a secondary concern). Usually I save space, but in the lookup tables you mention, I actually end up using more space.

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

I am interested, if you can give me a short overview (or link). I assume you do this so you have unique keys even when introducing data at separate places, that can't be connected to the network in real-time, for practical reasons. Is this correct?

Surely the GUID will use up more space. 16 bytes, right? This would make PKs and FKs larger, and joins slower.

Do you use GUIDs for most of your tables?

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

As I have been stating, I know there are some disadvantages, but I think they are outweighed by the advantages. So I use surrogate keys most of the time. It seems you do so, too - at least, part of the time.

I will try to take some time during the next few days to write a summary (for FAQ) of the different alternatives, and advantages and disadvantages of each approach.

Hilmar.