Re: Relationships: Meaningful char code vs numeric primary key

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),
> Nationality Description (e.g. BRITISH)

I question the existence of Nationality.Id; if Nationality.Code is unique, then it is a perfectly usable primary key for the table, and the Id column serves no purpose. If it doesn't serve a purpose, then it shouldn't even exist.

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

If Nationality.Id didn't exist, then Option A wouldn't exist either, and Option B becomes the obvious choice.

Assuming that Nationality.Id does exist for some reason, Option C amounts to storing the same data (namely, Nationality.Code) in two tables, and that's a Bad Thing(tm).

> 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

Give your DBMS some credit, and assume it can store, index, and retrieve character data efficiently. It probably can: after all, you're not exactly the first person who ever needed to do that. :-)

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

I would say that you are, at least until you demonstrate that you're not. Again, give your DBMS some credit: it can probably join tables (especially simple ones) efficiently, since that's such a routine thing to do.

I know your example is just an example, but within it, I don't think Nationality.Id serves any purpose, and so it shouldn't even exist. If it didn't exist, your question simply wouldn't arise.

A few rules from "The Elements of Programming Style" [ISBN 0-07-034207-5] seem to apply here:

• Make it right before you make it faster.
• Make it clear before you make it faster.
• Keep it simple to make it faster.
• Measure before making "efficiency" changes.

--