MySql Storing Types as enum vs tinyint

When I first started using mysql, I was using the enum type by default to store strings that you knew what the values were ahead of time. Years later I would read about using tinyint stored in a relational table to store the string. There are pros and cons to each one.

The enum type makes things easier, since everything is in one table and you won't have to join a table to get the value. If you know what the string value is gonna be a head of time, it is much more efficient to store it as a enum, instead of storing it as a varchar.

Here is an example from the mysql website:

Inserting 1 million rows into this table with a value of 'medium' would require 1 million bytes of storage, as opposed to 6 million bytes if you stored the actual string 'medium' in a VARCHAR column.

An enum is very efficient since behind the scene, mysql treats enum as numbers. As good as enums are there are a few issues with storing data as an enum. One of them, storing values you should know ahead of time. From experience enums can change quite often(they shouldn't). The challenge with changing enum values is you have to use the 'alter' command and if you have a giant table this can take time and lock up the tables. Another challenge is if you have a lot of values in your enum, this can become annoying to read. If I were to use an enum, I like to keep that values that are being stored under five.

The approach I have adopted over the year is storing values I know of in a relational table with a tinyint value as the id. There are a few benefits to doing it this way. Tinyint and enums values are stored as 1 bytes so their space requirement is about the same. Where I prefer tinyint over enums is, with a relational table, I can always add values to that table and not need to do an alter. This limits down time and you can always add values to the relational table. The one negative thing about doing this approach is if you wanted to fetch the values, you would have to do a join on the relational table.

These are two approaches to storing the same data in two different ways, each with their positive and negative. If I know I had three values to store for a column, I would use an enum, but if I knew a table would grow and possibly the values growing as well, I would use a relational table to store the values, it is up to the developer to decide what the best approach is.