Why use an int as a lookup table's primary key?

Question

I want to know why I should use an int as a lookup table's primary key instead of just using the lookup value as the primary key (which in most cases would be a string).

I understand that using a nvarchar(50) rather than an int would use way more space if it is linked to a table with many records.

On the other hand, using the lookup value directly would basically save us doing a join. I can imagine this would be a big saving if the join is always required (We're working on a web app so this counts quite a bit).

What are the advantages of using a int primary key (specifically for a lookup table) other than it being "the standard thing to do"?

Answer 1

The answer to your question is logical, not physical - the value you look up might change for business reasons. For example, if you index your customers by email address, what happens when an email address changes? Obviously this won't apply to all your lookup tables, but the benefits of doing it the same way across the entire application is that it makes your code simpler. If everything is integer → integer relations internally, you're covered.

Just read your comment to Sandy - perhaps in this case what you really want is a Check Constraint, not a foreign key/lookup table, e.g.:

create table icecream (flavour varchar(10))
go
alter table icecream add constraint ck_flavour check (flavour in ('Orange', 'Pista', 'Mango'))
go
insert into icecream (flavour) values ('Orange')
go
insert into icecream (flavour) values ('Vanilla')
go

Run this and you get:

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "ck_flavour". The conflict occurred in database "GAIUSDB", table "dbo.icecream", column 'flavour'.
The statement has been terminated.

This is an efficient, high-performance method, but the disadvantage of course is that adding a new flavour means a code change. I would advise against doing it in the application - because then you need to do it in every app that connects to this DB, this is the cleanest possible design because there is only a single code path for doing validation.

Answer 2

“Using lookup value directly” – its bit contradictory with the actual purpose of lookup table. Why you are keeping such a table? If it is not a lookup.
May be I misunderstood your question. Here is a lookup table definition from msdn

A lookup table is used to display information from one table based on the value of a foreign-key field in another table. For example, consider a table of Orders in a sales database. Each record in the Orders table includes a CustomerID indicating which customer placed the order. The CustomerID is a foreign key pointing to a customer record in the Customers table. When presenting a list of Orders (from the Orders table) you may want to display the actual customers name, as opposed to the CustomerID. Since the customers name is in the customers table, and you are presenting data from the Orders table, you need to create a lookup table, which takes the CustomerID value in the Orders record, and uses that value to navigate the relationship and return the more readable, customer name. This concept is known as a lookup table.

Can you elobrate the purpose of your lookup table? is it used to store some static data like the following and these records are not an input of other tables records?

Flavor table

Orange  
Pista  
Mango

If above is your situation, then I would like to recommend not to use lookup table; probably hardcode these list values in your web application. This way you can avoid unnecessary database querying.

Answer 3

Since you qualified your question with 'specifically for a lookup table,' the answer is probably simplified down to 'saves space.'

I think if you remove that qualifier, your question becomes 'Why use surrogate keys over natural keys?' I wrote the following in support of surrogate keys:

"Migrating one integer value instead of a wider compound key has numerous benefits. It provides nice consistency across the physical model, by and large saves more space than it costs and reduces I/O when compared to migrating compound keys; especially in a well-normalized model. Additionally, they simplify understanding of a model and query joins."

This is largely why it has "become the standard thing to do." The unfortunate bi-product is that people throw on a surrogate key and do not think what the candidate keys are... But now we are getting outside of your question :)

Answer 4

One of the reasons I always use is that if someone has misspelled a value in the lookup table, say Oraneg instead of Orange, it is far easy to change the value in the lookup table.

The lookup table with a number primary key will only require the value to be changed in the lookup table.

The lookup table using the values as their primary key will need to be changed in the lookup table and in every record in the main table where it was used.

Answer 5

When You define ID, You can also guarantee the uniqueness. But when You take, for example e-mail, as unique identifier, you move responsibility of uniqueness to untrusted 3rd side.