Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
4  
You could find good info and maybe even your needed answer in these 2 previous questions: Is there any benefit of a primary key that comprises all columns of the table? and Character vs Integer primary keys. –  Marian Mar 25 '11 at 13:28
    
You assume that a join is slower than fetching the data. Often that is not the case. –  JamesRyan Jan 10 '13 at 16:20

5 Answers 5

up vote 14 down vote accepted

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.

share|improve this answer
    
@Gaius- Good example... I prefer not to use Check Constraint for this type of scenario; main reason that it won't be maintainable (you've pointed it out as disadvantage). –  Coder Hawk Mar 26 '11 at 10:28
1  
@Sandy It really depends on the data, how often it will change, and where else it will be used. For example, if the constraint must be enforced by the DB, but the values might also be used to populate a drop-down menu, or in a report, then a foreign key would be more appropriate. Either way, I'd advise against doing it in the application. –  Gaius Mar 26 '11 at 10:33
    
I agree 'It really depends on the data' :) –  Coder Hawk Mar 26 '11 at 15:33
    
& @Sandy - Many thanks, learning something new every day –  Jaco Briers Mar 28 '11 at 5:40

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

share|improve this answer
    
good point, the purpose of my lookup table is basically just to enforce the different values that a column can have via a foreign key constraint. I agree that hardcoding it into the application may be another way of handling this. –  Jaco Briers Mar 25 '11 at 16:23
    
@Jaco Briers - See Gaius answer... use Check Constraint... –  Coder Hawk Mar 26 '11 at 8:46

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 :)

share|improve this answer

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.