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

There is a plenty of discussion in this group on this topic. you may want to search using a good search engine like google. But here is my two cents worth:

A primary key must be part of the data the table is representing. Good examples of a Primary key are: Emp_ID, Product_ID, SS_NO, tel_no, etc. They are naturally unique keys within the enterprise or the domain.

An auto-incrementing ID, though it satisfies the requirement of a primary key, is a wrongway to use it. Those keys do not have any meaning of their own. Worse yet, the value of such PK varies depending on the order of creation! When you export a table and import, you may not be able to use that key at all (it happens on Microsoft products!). Every table in your design must have a natural PK, which is part of the data itself. Period. No need for external intervention.

If you must have a auto-identity-key, it must be a surrogate key, with no bearing on the referential integrity.

Coming to the type of the key. It is tempting to think an INT/LONG is more efficient than a CHAR. Almost all commercial databases have mastered the art of storing/searching string-type index files. So performance should not be a concern. No real world data are Integers!! SS_Nos, Tel_nos, Zip codes, Emp_IDs, Product_Ids,.. none of them are pure numeric. Show me one real world key that is pure numeric. Probably dates, using the julian convention, can be expressed as longs. But you need to use a function to convert them to be of any use in the real world. So, in my opininon, you can never have numeric keys all the time.

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 Fri Mar 30 2001 - 04:13:12 CEST