Lookup tables - should I use an id column or just use the value?

Question

I have many lookup tables that look like:

id(pk) type(unique)
---
1 UNIVERSITY OF ARIZONA
2 UNIVERSITY OF MIAMI
3 TOKYO UNIVERSITY

Let's say type is a unique string in all cases.

I am questioning whether I should use the id(pk) or just do:

type(pk)
----
UNIVERSITY OF ARIZONA
...

Related: [What's the best practice for primary keys in tables?](https://stackoverflow.com/q/337503/190597) — unutbu, Feb 23 '19 at 19:01
This is an easily googled obvious faq that should not be asked or answered again. See [ask] & the voting arrow mouseover texts. — philipxy, Feb 23 '19 at 20:40

score 6 · Accepted Answer · answered Feb 23 '19 at 18:58

6

You should use the primary key. Here are some reasons:

Integers are generally more efficient than strings for joins.
Integers are definitely more efficient from a storage perspective -- the strings are longer than the numbers.
You might want to change the name one day, say, 'UNIVERSITY OF ARIZONA (TUCSON)'.
Primary keys are really what is expected for the foreign key relationship.

answered Feb 23 '19 at 18:58

Gordon Linoff

What about the case of filters (`WHERE/WHERE IN`)? Do integer columns provide more efficient lookups? I'm assuming yes due to the more efficient indexes/storage. So if I was building some sort of filter UI, the UI should pass to the server: `[{ id: 1, type: 'UNIVERSITY OF ARIZONA' }]` ( or simply `[1]`), then on the server I should map to the ids: `[1]`, then do a `WHERE IN [1]`? – atkayla Feb 23 '19 at 19:11
4

Also, integers don't have to deal with lowercase vs. uppercase, spelling differences, leading and/or trailing whitespace, and more .... – marc_s Feb 23 '19 at 19:21
Ok, I'm thinking since the primary key is on the integer `id`, I should be filtering on that column anyway since an efficient index is created on it by default, whereas `type` doesn't have an index. – atkayla Feb 23 '19 at 19:31
@Gordon Linoff Can you take a look at this? https://dba.stackexchange.com/questions/232952/joining-40-normalized-tables-for-200-columns-to-display-detailed-user-profile I wanted to get your opinion because you help out with all of my SQL queries. :) I'm finding that integer surrogate keys require so many extra joins that the benefits don't seem worth it compared to the performance penalty. – atkayla Mar 24 '19 at 20:20

1 Answers1