I'm creating a new table and I would like to do it right. The table will be a list of members with their Professional Type and Decile associated.
The Professional Type is a 3-character string. There are only 8 allowable strings for this field. Should I use ENUM or CHAR(3) or something better?
The Decile can be any number between 1-10, but only these numbers. Should I use ENUM or unsigned TINYINT or something better?
Which are the best options for efficiency?
ENUMs scare me because of any issue regarding adding/removing type values. You are probably better off representing both ProfessionalType and Decile as TINYINT or CHAR(3). Anything but ENUM.
See my past posts on ENUM :
Ocy 19, 2011 : Advantages and Disadvantages to using ENUM vs Integer types?Jan 24, 2012 : Is it possible to change ENUM() lists?For efficiency, please do not index this field alone, given such a low cardinality (8,10).
ENUM isn't a standard SQL datatype
Commented
Nov 2, 2023 at 21:34
I would add one more thing to all the previous answers and linked questions. It happened to me that I had a tinyint column to represent boolean. But one day I needed to split the true value to two separate values. Extending 0,1 to 0,1,2 was trivial, whereas changing it to string-enum would require more effort. Also reverting tinyint-enum of three values back to tinyint-boolean would be similarly easy.
If you're certain about having no more than 10 types, then use ENUM. Otherwise use a TINYINT, possibly pointing to a small table mapping these values to acceptable names/descriptions.
Avoid using CHAR(3); it is lengthier and prone to confusion (case sensitive? no? indexes are worse when insensitive).
