A schema keeps every enumeration - order statuses, country codes, payment types - in one generic table with columns like (id, category, code, label). What is wrong with that consolidation, and what would you do instead?

## The pattern Someone notices a dozen tiny two-column reference tables and consolidates them: ``` lookup(lookup_id, category, code, label, sort_order, is_active) ``` with rows like `(1, 'order_status', 'NEW', 'New')` and `(2, 'country', 'FR', 'France')`. Every referencing column then points at `lookup_id`. It is commonly called the one-true-lookup-table, and the motivation - fewer objects, one screen to administer them - is genuine. ## What it costs ### The foreign key loses its meaning The purpose of a lookup table is to constrain a column to a known set of values. `FOREIGN KEY (status_id) REFERENCES lookup(lookup_id)` constrains it to *some* row of the lookup table - which is nearly no constraint at all. An order can be assigned the status "France". The rule you actually want, "this column takes values from the order_status category", requires a workaround because a foreign key can only reference a whole table, never a subset of its rows. The standard workaround is a composite reference: give the lookup a unique key on `(category, code)`, add a redundant `status_category` column to the child pinned by a check constraint to the literal `'order_status'`, and declare the foreign key on `(status_category, status_code)`. It works and it is enforceable, but you have paid a redundant column on every referencing table to recover what separate tables give for free. ### One column, one type Different enumerations carry different payloads. A shared `value`/`label` pair forces everything into text. Numeric thresholds, dates and booleans lose their types, and validation that the engine could have done becomes application code. ### Every query carries a filter Joins must always add `AND lookup.category = 'order_status'`. Omitting it is not an error - the join still runs and returns extra or wrong rows. Multiplied across dozens of queries and reports, that is a steady source of silent bugs, and there is no constraint that catches it. ### Nowhere for per-enumeration attributes Real reference data is not just code-and-label. Currencies have minor-unit precision. Countries have two-letter and three-letter codes, calling codes, region groupings. Order statuses have an allowed-transition set and a terminal flag. A generic table can only grow nullable, weakly-named columns - `attr1`, `numeric_value`, `parent_lookup_id` - which drifts toward a fully generic attribute store and inherits all of its problems. ### Operational effects One table referenced by everything is a single point of contention: locks during administrative edits block unrelated work, and its row count and index depth grow with the union of all enumerations. Cache invalidation is coarse - touching one country evicts the cache for every enumeration. Access control is coarse too: granting someone the right to edit shipping methods grants edit rights over statuses and countries as well. Migrations become risky: renaming or deleting a code requires knowing every referencing column, and the schema no longer records which columns reference which category. ## What to do instead **One table per enumeration.** `order_status(code PK, label, sort_order, is_terminal)`, `country(alpha2 PK, alpha3, name, calling_code)`. Each gets a real foreign key that constrains the column exactly, each gets its own columns, its own permissions and its own cache. Small tables are cheap; a schema with twenty of them is not complex, it is explicit, and the object count is a poor proxy for complexity. **A check constraint for tiny, stable sets.** When the enumeration is code-only with no attributes and changes only with a release - a two-value channel, a three-value visibility - a `CHECK (col IN (...))` is simpler still, at the cost of a migration to change the list. Native enumerated types where the engine offers them are the same trade-off. **Judging between them:** does the set need attributes beyond the code? Does it change without a deploy? Do users administer it? Any yes points to a table; all no points to a check. ## Migrating away from an existing consolidated table 1. Add a unique key on `(category, code)` so the existing rows are addressable by meaning rather than surrogate id. 2. Create the per-enumeration tables and copy the relevant rows. 3. Add the properly typed code column to each referencing table, backfill from the lookup, and declare the real foreign key. 4. Move readers and writers across, one referencing column at a time, verifying counts. 5. Delete the migrated categories from the consolidated table and eventually drop it. While that runs, the interim composite-key workaround is worth applying to the highest-risk columns, because it converts an unenforceable relationship into an enforced one without waiting for the full migration. ## What interviewers listen for The distinguishing answer is the foreign-key argument: a constraint can reference a table but not a subset of its rows, so consolidation destroys exactly the guarantee that lookup tables exist to provide. Candidates who only say "it is messy" have not identified the defect.