What would you use ENUM for in SQL?
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.
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.