CS 601 – MySQL enum vs. reference table

During last nights class, I asked Professor Sheehan about using the enum MySQL data type vs using a reference table. The MySQL enum type is basically a simple list of valid values. For instance, in an employee database, we might have a number of employee types, manager, clerk, janitor, IT. We could then assign an employee one of these values. A reference table accomplishes the same task, but with a second table:

The question is, which is better? Professor Sheehan said he doesn’t use the enum type. I did some searching and found that reference table is much preferred method for a number of reasons most related to needing to change the allowed values. Even if you think you’re never going to have to do this, you never know. Here’s a few of the sites I found:

  1. 8 Reasons Why MySQL’s ENUM Data Type Is Evil
  2. MySQL Forums :: Newbie :: using enum versus tiny int and lookup table
  3. Mysql ENUM type vs join tables
  4. Enum Fields VS Varchar VS Int + Joined table: What is Faster? – Shows that in most cases, the performance loss when using reference tables is minimal.

Leave a Reply

Your email address will not be published.