What would you use ENUM for in SQL?

Question

I am trying to complete an assignment for my CIS class and I am required to include a data type called ENUM. I know that this stands for enumerated lists, but I am not sure when would be an appropriate time to use ENUM. A couple of the examples given in the book I am using were like a list of the continents. Could I use this to describe a list of departments for a workplace?

• Randolph West: I use it mainly for True and False. Short, static lists are better suited than department names, because you might want those in a table with other columns.
• user1822: @RandolphWest: for true and false you should use boolean
• user1822: You should also consider the limitations of enums, e.g. "Existing values cannot be removed from an enum type, nor can the sort ordering of such values be changed"
• Paul Spiegel: Yet I've never seen a really good reason to use ENUM. "a list of the continents" is a bad example. They should have its own table (entity).
• Randolph West: @a_horse_with_no_name Boolean isn't necessarily supported in all database systems. Some use integer fields. So for SQL Server I'd use a BIT, but others an ENUM works just fine.

Answer 1

ENUM datatype can be seen as a "human-readable" numeric value, commonly used for convenience and data storage efficiency.

Let's take your example with a table listing the countries and their respective continents.

Using VARCHAR

CREATE TABLE country_continent (
 country VARCHAR(100),
 continent VARCHAR(100)
);
INSERT INTO country_continent VALUES ('Kenya', 'Africa');
INSERT INTO country_continent VALUES ('New-York', 'America');
INSERT INTO country_continent VALUES ('Paris', 'Europe');

Using SMALLINT

For better storage efficiency you'd better use SMALLINT (2 bytes) instead of VARCHAR (generally 1 byte + the Length of the string) for the continent field:

CREATE TABLE continents (
 id SMALLINT,
 label VARCHAR(100)
);
INSERT INTO continents VALUES (1, 'Africa');
INSERT INTO continents VALUES (2, 'America');
INSERT INTO continents VALUES (3, 'Europe');
CREATE TABLE country_continent (
 country VARCHAR(100),
 continent_id SMALLINT
);
INSERT INTO country_continent VALUES ('Kenya', 1);
INSERT INTO country_continent VALUES ('New-York', 2);
INSERT INTO country_continent VALUES ('Paris', 3);

Using ENUM

This is where ENUM makes sense:

CREATE TABLE country_continent (
 country VARCHAR(100),
 continent ENUM('Africa', 'America', 'Antarctica', 'Asia', 'Europe', 'Oceania')
);
INSERT INTO country_continent VALUES ('Kenya', 'Africa');
INSERT INTO country_continent VALUES ('New-York', 'America');
INSERT INTO country_continent VALUES ('Paris', 'Europe');

With ENUM you get same storage efficiency (or even better) than SMALLINT, but the ease of use of a VARCHAR data type.

• carloswm85: "human-readable" numeric value, lovely definition for ENUM.
• Zack: With ENUM, you also don't get the benefits of using a lookup table. Obviously, we probably don't have to worry about the list of continents changing. But what if we wanted to store the names of continents in different languages? Or store attributes about continents (maybe shape data)? If you already have a continents lookup table, you just need to tack on some additional columns. Not so with enums.

Answer 2

Generally enums are for static lists, i.e. lists that never change. Departments is a bad choice because these can change at any time. Continents is good because these are highly unlikely to change, but countries would be bad because these do change. Think of things that are constants, such as seasons, months, day of week etc... You want to keep enums small. As a general rule that I apply personally, if a enum needs 10 or more entries, I create a regular list.

• Flourid: To add, the classic enum use case is gender (M/F, albeit you might want to include an O for Other nowadays) or other attributes that have to fit into a specific list and can only contain the expected results (e.g. eye color, graduation level). This will also allow you to skip input validation on the software side because any value in this column will be a valid entry from the list (since wrong entries will throw an error on inserting) and will prevent spelling errors (e.g. a varchar column "color" might also contain "blu", which will not show up when filtering for "blue")
• carloswm85: I liked that last rule of thumb.