One-to-many dictionary (lookup) table vs varchar vs enum? Imagine we have orders table, and an order has a status. Which of these three options is the best?


Use varchar for status column
Use enum for status column
Use separate status table, which has status_id int and name varchar, and in orders table keep status_id as a foreign key.


Which is the best approach? I suspect that using dictionary table is also better because it'd be faster to search statuses by int, instead of varchar.
Well, I am an advocate of ENUM -- at least in limited use.

I would use it for status with a small, reasonably static, list of possible values.  I would start with unknown to catch things that are typos.  ALTER TABLE has long been optimized to add a new option on the end of an ENUM list.

I would not use ENUM for continents.  If there are standard abbreviations, I would use a short VARCHAR for such.  For countries, I advocate

country CHAR(2) CHARACTER SET ascii


Using a lookup table can be very bad for efficiency.  A "star" schema in Data Warehousing can be terribly inefficient.  This happens when multiple JOINs are needed; ENUM avoids the inefficiency.  VARCHAR is bulky, which is a big issue in DW applications.

An ENUM acts like a string in many situations:  WHERE status = 'OK' is more readable than either of the alternatives.

Comparing two ints versus two strings: Ints is not enough faster to matter.


VARCHAR -- Use when bulkiness is not a problem
ENUM -- Use when bulkiness is a problem, and the number of choices is quite small and stable.
Dictionary lookup -- Use when multiple tables need to reference the same set of stuff, and/or when there is extra info in the Dictionary.