Should I use a code in my lookup table

Question

I am woking on an Orable database and I am adding a couple of lookup tables.

The general question is should the lookup table contain a code and a description and the code be the FK back to the main table, or should the lookup table contain only a description and that be the FK back to the main table.

I am arguing for the code/description pair. I feel that if I have type = Contractor and code = CN a stored proc should say where type='CN' rather than only having the type=Contractor and no code and saying this in the stored proc: where type='Contractor' Because what if I want to display: General Contractor to the user rather than Contractor. I would then have to change the stored proc. I feel that I should not have to do this. (changing a stored proc requires recompilation in dev, migration to test, retesting by the clients, and migrating a prod which requires going through a change control proccess which involves a two week waiting period; whereas modifying a record in a table does not require any of this)

My data modeler wants to use only the description. His main argument is that it would require an unnessesary join.

Which way should it be done? And if if should be done the code/description way how do i convince the data modeler?

Thanks!

type_cd    type_dsc
CN         Contractor
IN         Inspector

Answer 1

Summarizing all of the answers, I think there are four alternatives for the lookup table:

Alternative 1:
• Description (primary key, a longer varchar2 column)

Alternative 2:
• Code (primary key, a short varchar2 column)
• Description (not null, a longer varchar2 column)

Alternative 3:
• Id (a meaningless primary key, an integer value derived from a sequence)
• Description (not null, a longer varchar2 column)

Alternative 4:
• Id (a meaningless primary key, an integer value derived from a sequence)
• Code (unique key, a short varchar2 column)
• Description (not null, a longer varchar2 column)

The primary key column will be in the main table with a foreign key constraint on top.

Some characteristics per alternative:

Alternative 1:
• No join is required when querying the main table
• Clear meaning when doing ad-hoc queries on main table
• Requires more storage for main table
• Index on main table will be much bigger than in other alternatives
• Updating a Description value means maintenance trouble and possibly application downtime.

Alternative 2:
• Join is required when you want to retrieve the description value
• Join in not required if you want to filter on certain lookup values: you can use the code value for that.
• Pretty clear meaning when doing ad-hoc queries on main table
• Minimal additional storage requirements for main table
• Index on main table will be small.
• Updating a Description value is easy, however code is usually an abbreviation from the description. When updating a Description value, the code can become confusing.

Alternative 3:
• Join is required when you want to retrieve the description value
• When filtering on certain lookup values, you'd have to use the Description values in your queries as the Id's are meaningless.
• Meaning is not clear when doing ad-hoc queries on main table
• Minimal additional storage requirements for main table
• Index on main table will be small.
• Updating a Description value is easy and doesn't cause confusion as with Code values

Alternative 4:
• Join is required when you want to retrieve the description value
• Join is required when filtering on certain lookup values, you would use the Code value in the lookup table.
• Meaning is not clear when doing ad-hoc queries on main table
• Minimal additional storage requirements for main table
• Index on main table will be small
• Updating a Description value is easy and you can also very easily update the Code value as well to make it resemble the Description value. You may have to revisit some of your code when doing this, though.

Personal opinion:

I would look at how I plan to use the main table and the lookup table. Which queries will be important and have to run efficiently? Will the values ever change?

My personal choice would be alternative 2 or 4. I'd use alternative 2 if I was absolutely sure that the code value can never change. And this is rare. Country codes change, social security numbers change. Currency codes change, etcetera. So, most of the time, I'd choose alternative 4. I'd not be so concerned for an extra join, especially because the lookup table is a small table.

But: choose an alternative that suits your requirements.

Please feel free to edit the text when you know some more characteristics of an alternative.

Regards,
Rob.

Answer 2

Code/Description. That code value is (I assume) going to be a smaller, more efficient integer. Furthermore, you don't want to be in a situation where you need to update all your foreign keys just because a text description changes at some time in the future.

EDIT: Based on the sample code you just added, I would encourage you to make your code value an integer value rather than a string like 'CN', 'IN'. You want your key value to be agnostic to any "meaning" associated to the description. 'CN' still implies 'Contractor' and if/when that description changes to be 'External Resource' then 'CN' is going to be misleading.