Modeling enumerated types in the database

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:

  1. Using your database’s custom enumeration data-type if it has one
  2. Creating an int or char column and let the mapping live in the code
  3. Creating a lookup table

I generally lean towards the lookup table because:

  1. It’s highly portable
  2. It keeps your database self documenting
  3. It works well with any OR mapping layer
  4. 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.

This entry was posted in Database, Ruby, Software Engineering. Bookmark the permalink.

3 Responses to Modeling enumerated types in the database