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.

StackExchange Database Administrators Q#191434, answer score: 5