4

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?

3

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 :

For efficiency, please do not index this field alone, given such a low cardinality (8,10).

3

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.

2

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).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.