Enums And Lookup Tables
Many columns hold a value from a small fixed set, such as an order status of pending, shipped, or delivered. There are three ways to constrain such a column, with different tradeoffs.
- A check constraint lists the allowed strings directly on the column. It is simple but adding a value means altering the table.
- A native enum type defines the set once and reuses it. It is compact and self documenting, but in some databases reordering or removing values is awkward.
- A lookup table, also called a reference table, stores each allowed value as a row, and the main column becomes a foreign key into it.
The lookup table is the most flexible. Because each option is a row, you can attach extra attributes to an option, such as a display label, a sort order, an active flag, or a color. Adding or retiring an option is an ordinary insert or update, with no schema migration. The foreign key still guarantees only valid values are stored.
The tradeoff is an extra join to fetch the human label, and a tiny table that rarely changes. For truly static, tiny, attribute free sets an enum is leaner. When the set may grow or each value carries metadata, reach for a lookup table so options become data rather than schema.
Key idea
Use an enum for a tiny static set, but use a lookup table when options may change or carry their own attributes, turning allowed values into data.