Lookup Tables Best Practices: DB Tables... or Enumerations

Bishoy Moussa picture Bishoy Moussa · Jan 11, 2009 · Viewed 30.6k times · Source

If we have to store the available positions at a company (i.e. Manager, Team Lead, ... etc). What are the best practices for storing it? I have two opinions with comments... "sure, welcoming yours"

  1. Storing it as DB table with columns ID and Name, and deal with it using queries and joins.
  2. Storing it as Enum and forget about the DB table.

In my opinion, I will choose the first solution if I have changing items. So that I won't hard code these options as Enum.
I may choose the Enum solution, if I have no doubt that data won't change (for example, Gender: Male, Female).

NOTE: I code in English, and the UI Culture may be Arabic. If I will work with the Enum Solution, I will hard code the culture-based strings in the presentation layer, is it okay from the best practices perspective!!!!

I would like to know your opinions and if my thoughts correspond to what is most recommended "Best Practices"??

Answer

Cruachan picture Cruachan · Jan 11, 2009

Generally you should only use enumeration where there is a clear set of items that will not change, e.g. primary colours, or continent names. Otherwise lookup tables with appropriately implemented foreign keys are pretty much always the best option.

There is a possible variation on the lookup table option where you potentially have a large number of lookup tables for simple id/value relationships. A domain/lookup table pair can dramatically reduce this the number of tables required, albeit with some additional coding complexity. In this case you'd have a domain table

DomainID int identity
Domain   varchar(255)

and a key/value table

DomainID int
ID       int identity
Value    varchar(255)

Hence a row is added to the Domain table corresponding to each lookup table that you would otherwise use, and all (key-domain)/value pairs added to the value table. Apart from simplifying the database structure this approach also has the advantage that 'lookup tables' can be created in the application code dynamically, which in some applications can be extremely useful.