[Download Sample]
Common sense would have it that it is always better to use an integer column as a primary key. But just what are the pros and cons of using the lookup table scenario with integer keys? Let’s start with the benefits.
Wow, that’s a pretty hefty list of benefits. What about the cons?
As with all lists that try to be comprehensive, I will conclude with the common caveat that there may indeed be considerations left out, but I trust you will bear with me in limiting consideration to these for the purposes of the article.
Centralized name management can be a significant reason to use a lookup table, but that should not necessarily be the deciding factor. If your alternative named key value is not volatile, as is the case with country names and many other simple lookup tables, then this consideration may not weigh heavily in your decision. However, if you have no meaningful keys that are unique and non-volatile, you may certainly want to opt for the artificial key and lookup scenario.
Reduced storage space will only be important at the extreme high-end of database requirements. If you are looking at a potential difference of, on average, 15-20 bytes between an integer column and a variable character column, even when dealing with a million rows, you are only looking at a difference of about 15-20 megabytes, which is nothing in today’s world of high-capacity storage.
On the other hand, considering the added complexity of making the data usable because artificial integer keys are inherently meaningless, you may want to consider seriously other alternatives before jumping on the lookup table bandwagon. Consider that every time you want to present the data in any user interface or for exporting to other systems, you will need to do extra work to make the data meaningful.
To get the pros and cons of using a meaningful, human-readable key, you can, for the most part, invert the pros and cons lists above. The cons are addressed while a few of the benefits no longer hold true and can become sources of trouble.
User Comments