Let’s say you have an Employees table and you want a column to track the status of an employee such as [’employed’, ‘resigned’, ‘retired’, ‘terminated’, etc…]. You have a couple of options including:
- Using your database’s custom enumeration data-type if it has one
- Creating an int or char column and let the mapping live in the code
- Creating a lookup table
I generally lean towards the lookup table because:
- It’s highly portable
- It keeps your database self documenting
- It works well with any OR mapping layer
- It allows you to add new types via an administrative interface or SQL statement without altering the schema
The only downside is performance which usually makes caching of enumerated type lookup tables a necessity!
In Java/Hibernate land I handle the performance issue with a 2nd level cache such as ehCache. When working in Ruby on Rails I’ve started using the Acts as Enumerated plugin written by Trevor Squires‘ which handles the caching for you and is simple and elegant to use.