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

  1. F. Degenaar says:

    There’s a 4th kind of solution to this problem located somewhere between option 2 and option 3:
    Use option 2 in combination with Java 5 or Jakarta Commeons Enums, but provide only a message key in the toString() method like “com.foobar.EmployeeState.2”. The actual output string can be dealt with in a ResourceBundle.
    The icing on the cake is to build this ResourceBundle from a database table including the locale data. This way, one can lookup the text mapped to a particular state inside the database, I18N issues are elegantly resolved, and the output texts can be cached inside the application.

    Just my 0.02 EUR
    Fokko

  2. Uri Boness says:

    I don’t think it’s wise to make it a rule of thumb. In some cases, indeed the database is the proper place for the values for exactly the reasons you mentioned – this is usually the case where the enum type serves some purpose in the domain model. There are other enum types though that serve other purposes… an example for that is user role. In applications where the user roles are static, you can treat it as an enum type, but puting this in the database introduces another downside, which should not be taken lightly… the code *must* be in-sync. with the database at all time. In such situations, I prefer using approach no. 2. Of course, if your requirements can handle it, you can always save string keys instead of integers, so you still keep some documentation in the database (e.g. in case of java 5 enum, you can use the enum name().

  3. Steve says:

    Do you know of a good pattern for mapping java 5 enums to a lookup table in hibernate so :
    – You get the ease of using a Java enum in your code
    – You get the lookup table ensuing FK constraints on the DB as well as lookup tables being a common pattern in legacy databases.

    I’ve been thinking about it … but haven’t come up with anything better than mapping the class normally and then writing a regular java enum where I create a map of lookup table ids to the enum values (and a getter for the id from the enum) … it works, but it just seems wrong …

Comments are closed.