An order has a status, and it is one of a handful of values. There are three reasonable ways to model that, and teams argue about them on grounds that mostly do not matter while ignoring the one that does.
What changed in 2026
- Enum limitations stayed the deciding factor. The awkwardness of removing or reordering enum values continued to push teams toward alternatives.
- Check constraints gained favour. For fixed sets with no metadata, the lightest option became a more common choice.
- Lookup tables stayed standard for user-manageable lists. Anything an administrator edits kept its own table.
- Type safety moved to the application. Generated types from schema definitions reduced the argument for database-level enums.
The three options
|
Enum type |
Lookup table |
Check constraint |
| Storage |
Compact |
Foreign key |
The value itself |
| Add a value |
Usually easy |
Insert a row |
Migration |
| Remove a value |
Difficult |
Delete or deactivate |
Migration |
| Reorder |
Difficult |
Trivial — it is data |
|
| Attach metadata |
No |
Yes |
No |
| Requires a join |
No |
Yes |
No |
| Changeable at runtime |
No |
Yes |
No |
| Visible in the schema |
Yes |
Partly |
Yes |
The question that decides it
Who changes this list, and how often?
Nobody, essentially ever. Order status in a system where the workflow is fixed. A check constraint is the lightest thing that works — no extra table, no join, the valid values documented in the schema. Adding a value is a migration, which is appropriate because adding a status is a code change anyway.
Developers, occasionally. An enum or a check constraint both work. The enum is more explicit as a type and more awkward to modify; the check constraint is easier to change and slightly less discoverable. Either is defensible.
Administrators or users, through the product. A lookup table, without question. Anything editable at runtime must be data, and the other options require a deployment to change.
The list carries metadata. A lookup table. If each value needs a display label, a sort order, a colour, an active flag, or a description, that is a row with columns — the other options have nowhere to put it.
That framing resolves most disagreements. The performance argument — a join versus no join — is real and almost always negligible against a small lookup table that sits entirely in cache.
Enum-specific pain
Worth stating because it is where the surprises are.
Adding a value is generally straightforward, and in some engines it cannot be done inside a transaction, which complicates migration tooling.
Removing a value is not directly supported in several engines. The path is creating a new type, migrating every column using the old one, and dropping the old type — a real migration for what looks like a small change.
Reordering is similarly awkward, and it matters because enum ordering affects sorting and comparison.
Cross-database portability is poor; enum syntax and behaviour vary considerably.
None of that is disqualifying for a genuinely stable list. All of it is a reason not to reach for an enum by default.
A note on the lookup table
If you use one, two details matter.
Use a meaningful key, not an auto-increment integer. A status column containing shipped is readable in queries and logs; one containing 3 requires a join to understand, including during an incident when you are reading raw rows.
Deactivate rather than delete. Removing a lookup row breaks historical references. An active flag lets you retire a value for new records while preserving old ones — the same reasoning as any reference data.
Common mistakes
- A lookup table for a fixed three-value list. Join and complexity for unused flexibility.
- An enum for a list users edit. Requires a deploy to change.
- Integer keys in a lookup table. Unreadable data.
- Deleting lookup rows. Breaks historical references.
- Assuming enum ordering is arbitrary. It affects sorting.
- No constraint at all. A free-text status column accumulates typos and variants.
FAQ
Is the join really negligible?
For a small lookup table, effectively yes — it will be cached and the join is trivial. The cost only becomes interesting at very high query volume, where denormalising the label is an option.
What about application-level enums?
Useful for type safety in code and no substitute for a database constraint, since other writers bypass the application entirely — see check constraints.
Can I migrate between approaches?
Enum to check constraint or lookup table is straightforward. Going the other direction is also possible. Neither is free on a large table.
What if the list is long?
A lookup table, generally — long lists tend to be data rather than schema, and are more likely to change.
Where to go next
For the lightest enforcement option, read check constraints. For the deletion behaviour lookup tables need, cascade deletes, and for changing the constraint later, expand and contract migrations.