Design tip – Enumeration vs. Lookup

I wanted to provide a simple list of good practices for database design. As I started writing, I realized that the article would be very voluminous and difficult to write at once. So, I picked a single topic that I think the developer will eventually need to think about.

In my experience, the enumerated type in a platform solves one or more problems for the developer. So as the developer notices this possibility, they simply ask me, “Which one should I use, or which is better?”

In case you need some context. Enumeration is very useful in most object-oriented platforms. A fix set of values is coded into the definition and the values are stored directly into a database column. For example, a contact may have “Home,” “Work” and “Other” as values. The object or record would then become a member of one of three possible sets. Two advantages that I understand are: At runtime, the error message is quite clear and meaningful when you assign an out of bound value. Also, some IDEs display the available choices for the enumerated type when coding thus avoiding mistakes.

So, which is better? Like most database questions the answer, it depends. I admit that from my perspective enumeration or ENUM datatypes solve some database problems too. The database column can be defined as an ENUM type. This will provide database integrity protection for the developers that come after the initial development. This is also quite helpful because the person who at a later time needs to analyze the data in the database does not need to look into the application code to find the valid values or query all the records to see what values are there. The ENUM in the column definition list them all. So I only see the benefit and would generally support ENUM type use.

A different view I’ve read in developer forums regarding enumeration and lookups is that enumerations is for something simple that never changes, example left or right. Lookups are used for everything else because of the following two reasons: database integrity and simplicity when adding a new type. From the database and long-term maintenance point of view, I disagree.

The lookup table for the purpose of referential integrity is not a valid argument. The same is achieved by the ENUM type. You also avoid an unnecessary table which I consider a nice benefit. If you are developing a platform, you might want to consider a lookup table but if you know in advance that you have a reasonable finite list you should think otherwise.

The argument that you can dynamically add a value to a table to change a type can be a good one. I would add the absolute condition that part of the development needs to have a user interface so there would be absolutely no special intervention required. If you are asking a developer to manually insert records in a type, you can just as easily modify the type in the table. So there is no real benefit.

Something to think about.

Published by Marek

Just a Database Administrator from Montreal.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: