I'd like to give you my thoughts about the ENUM data type, which has strongly been discussed in various blog articles recently.
Ronald Bradford showed a nice way how to set up a reference table that's combined with the original table via foreign key constraints. I think, he's right that this is a more proper way to restrict a field to a range of allowable values - read here: http://blog.arabx.com.au/?p=87.
However, I still believe, if you only have up to maybe 5 or 6 values that very likely never change, the ENUM data type does a great job. It's easy to handle and it does exactly what it's supposed to do.
But even though more than 65,000 different values are allowed in an ENUM field, I think it shouldn't be used for many different values - in that case it's probably a better choice to set up a reference table.
Subscribe to:
Post Comments (Atom)
2 comments:
I wouldn't mind seeing the enum type changed to 1 byte unsigned instead of 2. This way people are less apt to use it for huge lists .
The tests for "enum or not to enum" should be:
1) immutability. If the enums will ever change, they should not be enums,
2) encapsulation. If the enums are ever needed outside their own table, they should not be enums.
Otherwise, they make good sense from an immutability and encapsulation point of view -- no matter how many there are -- and the performance improvement is just sugar.
Post a Comment