Advantages and Disadvantages to using ENUM vs Integer types?

Question

Lets say in some random table, you have a column named status. It's real-world values would be either enabled or disabled.

Is it better for this column's data type to be an int/bool (1 or zero) or to use ENUM with the values being enabled and disabled? What are the advantages or disadvantages?

Let's say instead of just two valid status's, you have 4 or 10 or even more? Do the advantages and disadvantages sway to one side or the other as the number of required values increases?

Answer 1

I found a very bizarre but informative article about 8 reasons why one should not use ENUM.

Even without the article, I know

• there is no easy method for adding new values, some techniques are very high risk
• numbers should never be used
• only use strings (@DTest already mentioned this in his answer)

Answer 2

Well, first off we have the storage requirements. I'm going to assume you meant a tinyint (instead of int).

• ENUM takes 1 byte (if under 255 values) or 2 bytes (up to maximum of 65,535
• TinyInt takes 1 byte (maximum of 255 values)
• Boolean is a synonym for TinyInt

So, on the surface, they're all the same. ENUM does take up some metadata for the string value associated with it though (older src)

I would say as you add more values though, any advantage starts to swing away from ENUM. Especially if you add the values after the table is already in use, because you have to alter the table structure to accommodate.

What is the advantage of using an ENUM? A string representation of what the value means. That's it, as far as I'm concerned. How valuable that is depends on your application.

Answer 3

I find ENUM is a short form definition of a code table. Its main advantage is that it avoids the code required to join and show the code description. It also eases setting the values if they arrive in string form.

I find it has the following disadvantages:

• No capability for additional metadata about the code.
• Difficult to add or disable values. (Disabling codes can be accomplished with a trigger and expiry field.)
• I18n in the database can not be done.
• Not reusable across tables.