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:
- 8 Reasons Why MySQL’s ENUM Data Type Is Evil
- MySQL Forums :: Newbie :: using enum versus tiny int and lookup table
- Mysql ENUM type vs join tables
- 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.