Re: Use of numeric pseudo-key vs. text key

It's normal to have a pseudo or artificial key for tables that only contain long natural unique identifiers - it does save space.

However, I don't create such keys for every table (as most people seem to) because it can slow performance down.

The benefit of cascading natural short natural keys is that the information is readily available and tables can be jumped in the joins.

E.g. suppose there are three tables Table_1, Table_2 and Table_3 with their own ids and the following structure:

Table_1        Table_2               Table_3
T1_id (PK)     T2_id (PK)            T3_id (PK)
T1_text        T2_text               T3_text
               T1_id (foreign key)   T2_id (foreign key)

If you start at Table_3 (e.g. if it's where the volatile data is) and need to discover T1_text you must make two joins (i.e. via Table_2).

If Table_2 included the foreign key column T1_id as part of it's primary key, T3 would also contain a T1_id and could navigate directly to Table_1's data in one join.

I hope this makes some sense!

Cheers,

Tim

"AnObserver" <nospamx_at_nospamx.com> wrote in message news:u1NjwLvsAHA.344_at_cpmsnbbsa07...
> Can anyone point me to some guidelines as to when you should use a
> pseudo-key like an autoincrementing number as the primary key of a
 table
> instead of a longer text value (which will also be unique).
>
> My instinct is that use of the pseudo-key makes the database smaller
 (and
> perhaps faster) since less space is required for foreign key storage
 and
> less data must be compared for joins. It also allows changes to the
> associated text value without concerns about relationships, so I've
 gotten
> into a habit of doing this almost universally.
>
> Comments?
>
>
Received on Sun Mar 25 2001 - 17:13:42 CEST