Relationships: Meaningful char code vs numeric primary key

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 Fri Jun 14 2002 - 15:13:12 CEST