Why You Should Replace ENUM With Something Else
Out Of Date Warning
Languages change. Perspectives are different. Ideas move on. This article was published on November 25, 2009 which is more than two years ago. It may be out of date. You should verify that technical information in this article is still current before relying upon it for your own purposes.
- Avoiding Notices: When to Use isset() and empty()
- Configuring PHP: Essential INI Settings
- Accessing Databases with PDO: A Primer
- To The New PHP Programmers…
- How To Write A Function In PHP
- Five Cool PHP Array Functions
- Micro Optimizations That Don’t Matter
- Adapting The Joel Test To Web Development
- Exceptional PHP: Introduction to Exceptions
- Suhosin: The Invisible Hand Of PHP
- Why You Should Replace ENUM With Something Else
One of the most hotly contested points of my article on database design was the suggestion that developers drop the use of ENUM and use something else instead. Lots of people argued in favor of ENUM; however, there are several good reasons why developers should reconsider ENUM and use it sparingly.
There are three core reasons why ENUM is a data type that should be reconsidered.
ENUM requires a rebuild of the table when adding a value to the middle of the set.
While its true that adding an ENUM to the end of the set doesn’t require a rebuild, often this is impractical. Adding a new value to the ENUM definition will require MySQL to rebuild the entire table – less than optimal for large tables. The time required will depend on how large your tables are but millions of rows will take considerable amounts of time.
ENUM values are ordered in the order they’re added to the database
If you’ve ever done an ORDER BY on an ENUM column, you’ll notice that MySQL organizes them via the order they were added to the ENUM, rather than alphabetically or numerically. This ties into the first point, because if you want to order values in, say, alphabetical order, you have to reorder the ENUM in alphabetical order, and this results in a rebuilding of the table by MySQL.
It’s also worth nothing that for developers on your team, they may be extremely frustrated when they discover that the column is ordered “incorrectly”; they might expect it to be ordered alphabetically and since it’s not, they will try and figure out why. They may not know the database as thoroughly as the database administrator or developer who put it together, and thus might not know about the ENUM fields.
ENUM values do in the database what should be done in the model.
Contrary to what many people believe, the database is not the model. The model is the domain logic which takes the raw data and turns it into data that the application uses. For example, you may store 0.25 in the database but convert that to 25% when you display it in your view; it’s not stored as 25% in the database, though.
The model should be enforcing the constraints on the data types and values going into your database, not the database itself. The database is simply the storage location for the data the model needs. The same logic tends to apply for triggers and stored procedures, limiting their authorship to manipulating data in the database when doing so via the model would be too time-consuming or resource intensive (the database is generally going to be faster at manipulating database data).
Cases For Using ENUM
When my last blog post published, one of my good friends Eli White pointed out that ENUM could be useful for columns that had data that would always fit into one particular set of values. For example, he said gender would be one such field that might be served well with an ENUM. And if anyone should know about database types, it’s Eli, since he’s a former Digg employee.
The bottom line is that ENUM has its place, but should be used sparingly. The model should enforce the constraints, not the database; the model should handle interpreting raw data into useful information for your views, not the database.