One True Lookup Table (OTLT)

The one true lookup table (OTLT), also called the massively unified code table, collapses every small reference list — countries, statuses, currencies, roles — into a single generic table with columns like code_type, code_value, and description. Instead of a statuses table and a countries table, there is one codes table holding all of them, distinguished by code_type.

It feels tidy: one place for all lookups, one admin screen to manage them. In practice it sabotages the relational model.

Why It Happens

Reference lists are numerous and individually trivial, so creating a separate table for each feels like clutter. A single shared table seems to reduce table count and centralize maintenance. ORMs and admin frameworks sometimes encourage a generic configuration table. The decision is usually made early, before the cost of lost integrity is visible.

Why It Hurts

Foreign keys cannot enforce that an order.status_id points at a status rather than a country, because both live in the same table. Integrity must be re-implemented in application code, where it is inconsistently applied. The code_value column has one type for all categories, forcing awkward casts. Queries must always add AND code_type = 'status', which is easy to forget and slow to filter. The table becomes a contention point and an indexing compromise serving many unrelated access patterns. Ambiguous surrogate keys make accidental cross-domain joins possible.

Warning Signs

• A codes or lookups table with a code_type discriminator column.
• No foreign keys from business tables to their reference values.
• Queries everywhere include a literal code_type filter.
• Different categories need different columns, so the table grows generic nullable fields.

Better Alternatives

Give each reference list its own dedicated lookup table with a meaningful name and proper foreign keys; this restores referential integrity and lets each table carry the columns it actually needs. For small, stable, code-only sets, native enum types (where supported) are even simpler and fully type-checked. Normalization guides where a separate table earns its keep versus an enum.

How to Refactor Out of It

Enumerate the categories hiding inside the OTLT. For each, create a dedicated table and copy the relevant rows, assigning stable keys. Add foreign keys from referencing tables to the new lookup tables, fixing any orphaned references uncovered in the process. Migrate queries to drop the code_type filter and join to the specific table. Retire the OTLT once nothing references it. The result is a schema the database can validate for you instead of one your application must police.