Re: Relationships: Meaningful char code vs numeric primary key

All identifiers are character based. Numerics are values, not identifiers. A PK is an identifier, not a value. So, you should make all keys character based.

All naturally occuring keys in real life are character based - e.g. phone numbers, zip codes, SSNs, country names, even dates. Tell me one naturally occuring key in pure numeric format. You cant find any.

The commercially available databases deal with character strings for their bread and butter. They have mastered using character strings to such an extent, that you can use characters for PKs without much hesitation.

having said that, if you have some lookup tables, for internal use of the application (e.g. a list of status codes, a list of available languages, etc.) you can use numeric PKs for such tables.

"Paul Rhoades" <Paul_Rhoades_at_hotmail.com> wrote in message news:aa4fa6ea.0206140513.39a3458d_at_posting.google.com...
> I have been mulling over the following scenario for some time and have
> never come to a conclusion so would be interested in other's views &
> opinions.
>
> As a trivial example, say you have the following two tables
>
> User Nationality
> ---- -----------
> Id (unique numeric PK) Id (unique numeric PK)
> Name Code (unique 4 character meaningful code,
> e.g. BRIT)
> Nationality Description (e.g. BRITISH)
>
> Now if we have a n-1 relationship between these two tables I can never
> decide which of the following three options I should use to implement
> the relationship.
>
> Option A : Numeric User.Nationality references the Nationality.Id
> field
> Option B : Character User.Nationality references the Nationality.Code
> field
> Option C : User contains the Nationality Code and Id where the Id is
> used for the reference and the code is available as a more meaningful
> value for display.
>
> The issues here that I can't make a decision on are that I believe the
> Id reference will be more efficient as it is numeric based as opposed
> to character based, but this will be a meaningless number within the
> User table, whereas if I include the meaningful code within the User
> Table then this be displayed from the User table without doing a join
> to the Nationality table.
>
> I guess the fundamental question is am I splitting hairs regarding the
> performance of a join using a character based field as opposed to a
> numeric field?
>
> Paul.
Received on Sat Jun 15 2002 - 05:58:33 CEST