Re: Relationships: Meaningful char code vs numeric primary key

"Sirin Venigalla" <a_at_abc.com> wrote in message news:JXyO8.15623$uk2.7163507_at_twister.nyroc.rr.com...
> 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.

temperature
age
employee number (at most of the places I have worked) latitude
longitude
atomic number

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

I agree. Even small footprint dbmses that I use compress character keys etc.

> "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 - 07:15:16 CEST