Lookup tables (or code tables, as some people call them) are usually a collection of the possible values that can be given for a certain column.

For example, suppose we have a lookup table called party (meant to store information about political parties) that has two columns:

  • party_code_idn, which holds system-generated numeric values, and (lacking business domain meaning) works as a surrogate for the real key.
  • party_code, is the real or “natural” key of the table because it maintains values that have business domain connotations.

And let us say that such table retains the data that follows:

 | party_code_idn | party_code |
 |              1 | Republican |
 |              2 | Democratic |

The party_code column, which keeps the values 'Republican' and 'Democratic', being the real key of the table, is set up with a UNIQUE constraint, but I optionally added the party_code_idn and defined it as the PK of the table (though, logically speaking, party_code may work as the PRIMARY KEY [PK]).


What are the best practices for pointing to lookup values from transaction tables? Should I establish FOREIGN KEY (FK) references either (a) directly to the natural and meaningful value or (b) to surrogate value?

Option (a), for example,

 | candidate_idn | party_code |  city   |
 |             1 | Democratic | Alaska  |
 |             2 | Republican | Memphis |

has the following properties1:

  1. Readable for the end user (+)
  2. Easy to import-export across systems (+)
  3. Difficult to change the value as it needs modification in all referring tables (-)
  4. Adding new value is not costly (=)

I think it is almost like “pass by value”, to draw an analogy from function call in application programming jargon.

Option (b), for instance,

 | candidate_idn | party_code_idn |  city   |
 |             1 |              1 | Alaska  |
 |             2 |              2 | Memphis |

has the properties below:

  1. Not readable for the end user (-)
  2. Difficult to import-export as we need to de-reference it (-)
  3. Easy to change values, as we are only storing references in transaction tables (+)
  4. Adding new value is not costly (=)

It is very similar to “pass by reference”, if comparing to function call in app programming parlance.

Import-Export can also be done in a different way, i.e., just by populating the look-up table again and then re-seed the surrogate column. I hope I am getting this right, this is something I have just heard as a possibility.

1. Note that +, - and = indicate the benefit of those properties.


Quite importantly: Is there a difference between a lookup (or code) table and a FK reference if we are just going to use the latter approach? I think they work just the same.

Related resources


3 Answers 3


By IDN, I take it you mean an IDENTITY, SEQUENCE or AUTO_INCREMENT field? You should take a look here and here.

Note, section 5 (Misusing Data values as Data Elements) of the first reference, underneath figure 10

Of course you can have a separate table for the sales persons and then reference it using a foreign key, preferably with a simple surrogate key such as sales_person_id , shown above.

So, this expert thinks that you should "deference" surrogate keys. It is really quite a basic SQL technique and shouldn't cause problems in your day-to-day SQL. It appears that there is an error in figure 10 - the sales_person in SalesData should be a surrogate key (i.e. a number), not text. I'm inferring this from the quote above.

What you should avoid at all costs is the temptation (very common for novice database programmers) to commit the error outlined in section (1) Common Lookup Tables. This is commonly called the MUCK (Massively Unified Code Key) approach (not by accident :-) notably by Joe Celko, also sarcasticlly known as the OTLT - One True Lookup Table) and leads to all sorts of difficulties. Novice programmers appear to feel that a single code/lookup/whatever table is "cleaner" and will be more efficient when nothing could be further from the truth.

From the second reference above:

Normalization eliminates redundant data, thus making the task of enforcing data integrity vastly simpler, but the process of creating a MUCK is something else entirely.MUCK's do not eliminate redundant data, rather they are an elimination of what are PERCEIVED to be redundant tables, but as I will demonstrate, fewer tables does not equal simplicity.

You might also want to take a look at the related EAV (Entity Attribute Value) paradigm which I deal with here.

  • 1
    By IDN, I meant the auto generated foreign key. I don't use Common Lookup Tables, not sure how you thought I used that? We use like hundreds of Code Tables actually. It seems really odd someone would do that in a unified table. But its good to know such a pattern exists and should be avoided. EAV seems interesting. So the consensus is that I should dereference using IDN i.e surrogate key?
    – Nishant
    Commented Jul 3, 2016 at 5:38
  • 1
    The "dereferencing" stratagem certainly appears to be the majority approach. Why not experiment a little and see how you get on? Pick some natural keys and see how your SQL works out - then specify a surrogate and mess around with that for a while. Celko and Pascal would be respected in the SQL/Relational world, but I've seen people arguing with them saying that their approach is too doctrinaire and purist - and that "real-world" systems have to use surrogate keys. If your natural key is three fields and that is further a FOREIGN KEY in another table, it can get pretty messy but YMMV.
    – Vérace
    Commented Jul 3, 2016 at 5:43
  • Yeah tbh I had this purist thinking and I was like why do ppl use surrogate keys! And then some use cases seemed really difficult to handle in the purist world. I felt that surrogate approach is more easier though you have some disadvantages of importing and exporting. Indeed the combination scenario can be trickier. Btw Code Tables are not much different from Foreign Key in the surrogate scenario right? I mean the logical distinction exists buts its nothing but a Foreign Key.
    – Nishant
    Commented Jul 3, 2016 at 5:50
  • 1
    You can enforce your natural keys via UNIQUE CONSTRAINTs and NOT NULLs - well, your Code Table entries are FOREIGN KEYs in the tables which use/refer to them - so the concepts are related, but not the same. The surrogate key of the Code Table is the field which appears in the "child" table - less legible certainly, but an INT isn't very big - not much space required which is an advantage of surrogate keys.
    – Vérace
    Commented Jul 3, 2016 at 6:13

There is a third approach which has some of the advantages of your two options - put an actual code in the code table. By this I mean a short character sequence that captures the essence of the full value and is unique. For your given example it may be

Idn: 1
Name: Democrats
Code: D      (or DEM)

The Code is carried into transactional tables as a foreign key. It is short, intelligible and somewhat independent of the "real" data. Incremental changes to the a name would not suggest a code change. Should Republicans decamp en masse, however, a change of code may be necessary, with its attendant problems that a surrogate id would not incur.

This style has been termed an abbreviation encoding. I can recommend Celko's writing on this. Google books holds several examples. Search for "Celko encoding".

Other examples: 2 or 3 letter encodings for countries, 3-letter encoding (GBP, USD, EUR) for currency codes. Short, self-explaining and not changing (and there is an ISO for them).

Each of Idn, Code and Name will be unique so each is a candidate key and any one could be chosen as the primary key. So for the example given Idn could be removed from the table definition and Code used instead. Different DBMS handle integers and strings in their own way, so there may be performance considerations. It may be useful to have Idn as the FK in some tables and Code in others.

  • With this approach, do we still need Idn column? Is it okay to just make Code column as primary key?
    – Fahmi
    Commented Sep 8, 2020 at 17:21
  • 2
    @Fahmi Each of Idn, Code and Name will be unique so each is a candidate key and any one could be chosen as the primary key. So yes - Idn could be removed. Different DBMS handle integers and strings in their own way, so there may be performance considerations. It may be useful to have Idn as the FK in some tables and Code in others. Commented Sep 9, 2020 at 10:31
  • 1
    A cautionary tale with this approach is that these codes can become confused and ambiguous. I've seen on a large production database where developers have completely mixed up the codes with polar opposite meanings. For example, 'A' = 'Approved' and 'C' = 'Cancelled', but then another developer insists 'A' = Abandoned and 'C' = Completed.
    – user339
    Commented Aug 11, 2023 at 11:05

The answer is, as usual, it depends, in this case, primarily on whether your values are subject to change.

Although there are lots of competing principles, the primary principle at play is that in a relational database you want to avoid updating primary (and therefore foreign) key values at all cost. Others might argue that some of the other factors you've brought up, like readability for example, are most important. However, I would counter that data which is semantically broken doesn't help you whether it's readable or not.

Based on too many years of bitter experience, I use the rule of thumb that any data element which could change will probably eventually change. This is why I favour meaningless primary keys for almost every table. There are exceptions. For example if there is a standards body (e.g. ISO) which has declared a lookup code value (e.g. USD for United States Dollars) then I would trust that to be stable.

For your specific use case of political party name, I present for your consideration the mainstream federal conservative party in Canada, which was known for many years as the "Progressive Conservative Party", but after a merger with (take-over by) the Reform Party, they became, briefly, the "Conservative-Reform Alliance Party" and then, after someone bothered to think through the ramifications of abbreviations, they settled on the "Conservative Party of Canada". So here is a concrete example of where using a surrogate key for a lookup table would allow you keep your data accurate and consistent.

On the other hand (there's always at least one) if the lookup table value is not that pervasive or not that central to your data domain then it might be worth the risk of using a natural key. All design is trade-off.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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