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

Solution 1:

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.

Solution 2:

Option 1: Storing it as DB table with columns ID and Name, and deal with it using queries and joins is the minimum that you should do.

From "Five Simple Database Design Errors You Should Avoid":

While application-enforced integrity is sometimes favored by developers, it remains true that the DBMS must still be the centralized enforcer of all integrity.

The recommended best practice is to implement your database as if it knows nothing about the user interface. That is, the database should enforce all rules pertaining to the data; in this case, the database should be enforcing what values are appropriate. To enumerate what values are appropriate, a lookup table for each type of lookup value is usually the way to go. Many times, applications come and go, yet the database remains and is reused.

If you want to enforce enumerations in the application, you can certainly do so, but whatever you do, make sure the database does its job as a database and maintains the integrity of the data. If it's troublesome, go through the trouble; you are laying the groundwork. In "Database Design and the Leaning Tower of Pisa," the writer emphasizes why laying the groundwork properly in a database is so very important.

I hope this helps.