Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
up vote 2 down vote accepted

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

share|improve this answer

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

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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