A table stores contact details as columns named phone1, phone2 and phone3. What problems does that layout cause, and what would you replace it with?
answer
- Names differ only by a number = slot pattern
- OR across columns, one index per slot
- Cannot constrain the set: unique, at-least-one, one-primary
- Fix: child table + FK + partial unique index
- Fixed pair like latitude/longitude is not this
basics
~20 sNumbered columns hard-code a limit, force every query to repeat itself across all three columns, cannot be indexed or constrained as a group, and need a schema change to add a fourth. Replace them with a child table holding one row per phone, keyed to the parent.
solid answer
~60 sRepeated numbered columns are a repeating group flattened into a row. The costs: - **Arbitrary cap.** Three is a guess; a fourth number requires DDL plus changes in every query and every piece of code that touches the group. - **Query duplication.** "Find the customer with this number" becomes an OR across three columns; searching all values needs a UNION. None of it uses one index well - you need an index per column. - **No group constraints.** You cannot make the set unique, cannot require at least one, and cannot enforce ordering or labels. - **Sparsity.** Most rows carry nulls, and "is slot 2 empty or is it the second number?" becomes ambiguous when slots are shuffled by updates. - **No per-value attributes.** There is nowhere to put type, verified flag or added-at without inventing `phone2_type` and multiplying the problem. **Fix:** a child table `customer_phone(customer_id, phone, phone_type, is_primary, ...)` with a foreign key, a uniqueness constraint on the values that must be unique, and a partial unique index enforcing at most one primary per customer. Fixed-arity columns that are genuinely fixed and semantically distinct - `latitude`/`longitude`, `first_name`/`last_name` - are not this anti-pattern.
code
sql · 14 linesCREATE TABLE customer_phone (
customer_phone_id bigserial PRIMARY KEY,
customer_id bigint NOT NULL REFERENCES customer(customer_id) ON DELETE CASCADE,
phone varchar(32) NOT NULL,
phone_type varchar(16) NOT NULL,
is_primary boolean NOT NULL DEFAULT false,
created_at timestamptz NOT NULL DEFAULT now(),
CONSTRAINT uq_customer_phone UNIQUE (customer_id, phone)
);
CREATE UNIQUE INDEX uq_customer_primary_phone
ON customer_phone (customer_id) WHERE is_primary;
CREATE INDEX ix_customer_phone_lookup ON customer_phone (phone);go deeper
Name the concrete pains - the cap, the OR-heavy queries, the nulls - and propose the child table with a foreign key.
Add the constraints the child table makes possible, including at-most-one-primary via a partial unique index, and the indexing argument.
Cover the incremental migration path on a live table and the boundary case where fixed named roles are correct.
Weigh migration cost against ongoing friction, and set the policy that stops new slots being added while the migration proceeds.
## What the pattern is A repeating group is a set of same-meaning values belonging to one entity. Numbered columns encode it positionally in a single row: `phone1, phone2, phone3`, or `tag1..tag5`, or `child_name_1..child_name_4`. Each slot holds a value of the same kind, and the number is nothing but an index. ## Why it hurts **The cap is arbitrary and wrong.** Whatever number was chosen, the real world will exceed it. Extending means a schema migration plus edits in every query, every mapper, every report and every export - the change ripples through code that had no reason to care. **Every query duplicates itself.** Lookup by value becomes `WHERE phone1 = ? OR phone2 = ? OR phone3 = ?`. Listing all values requires unpivoting with a UNION. Counting how many contacts a customer has means counting non-nulls across columns. Each of these grows with the arity, and a query written when there were three columns silently misses values once a fourth exists. **Indexing degrades.** A single index cannot serve an OR across three independent columns; you need one index per slot, tripling write cost and storage for one logical lookup. A child table needs exactly one index on the value column. **Constraints become impossible.** Common requirements that you simply cannot express: the values must be distinct within a row; at least one must be present; exactly one is the primary; each has a type from a fixed set; the set is ordered. In a child table each of these is a normal constraint or a partial unique index. **Slots carry no identity.** Deleting the middle value leaves a hole, and whether code compacts the slots or leaves the gap is undefined behaviour that different call sites will get wrong. Two rows with the same set of numbers in a different slot order compare unequal. **No room for per-value attributes.** The moment the business wants to know when a number was verified, the layout demands `phone1_verified_at, phone2_verified_at, ...`, and column count grows multiplicatively. **Row width and update cost.** Mostly-null wide rows waste space and, in engines that rewrite the whole row on update, make every write more expensive than it needs to be. ## The replacement ``` customer(customer_id PK, ...) customer_phone( customer_phone_id PK, customer_id FK -> customer, phone, phone_type, is_primary, created_at ) ``` With: - a foreign key so orphan values are impossible, - `UNIQUE (customer_id, phone)` so a number is not stored twice for one customer, - a partial unique index on `(customer_id) WHERE is_primary` so at most one primary exists, - an index on `phone` for reverse lookup, - optionally a check that `phone_type` is from an allowed set, or a foreign key to a lookup table. Queries become single-shape and arity-independent: joining, filtering, aggregating and paging all work with no knowledge of how many values exist. ## When fixed columns are correct The test is whether the positions are **semantically distinct** or merely **indexed**. - `latitude` and `longitude` are distinct concepts that always come as a pair; `coordinate1`/`coordinate2` would be the anti-pattern, `latitude`/`longitude` are not. - `home_phone` and `mobile_phone`, when the business genuinely has exactly those two named roles with different rules, are named roles rather than slots - though this often becomes a slot pattern in disguise once someone adds `mobile_phone_2`. - `billing_address_id` and `shipping_address_id` are two roles, not two slots. The warning signs of a genuine slot pattern are: the names differ only by a number; adding another is conceivable; code loops over them or repeats itself per slot; nulls dominate the higher-numbered ones. ## Migrating an existing table Work incrementally and non-destructively: 1. Create the child table and its constraints. 2. Backfill by unpivoting the non-null slots, preserving slot order as an ordinal if order matters. 3. Dual-write from application code so both representations stay current. 4. Move readers to the child table one at a time, verifying counts match. 5. Once no reader remains, drop the numbered columns. Stop-the-bleeding rule while the migration is in flight: no new code may read or write the numbered columns, and no fourth slot is ever added. ## What interviewers listen for The weak answer says only "it violates first normal form" and stops. The strong answer names the operational consequences - query duplication, per-slot indexes, unenforceable constraints, migration cost on every extension - proposes the concrete child table with its constraints, and knows the boundary case where fixed columns are the right design.
- When are several same-typed columns on one row the right design rather than this anti-pattern?When the positions are semantically distinct roles rather than indexed slots, and the arity is fixed by the domain itself. Latitude and longitude, or billing and shipping address references, are named roles with different meanings and different rules. The tell for the anti-pattern is that names differ only by a number, another one is conceivable, and code repeats itself once per slot.
- How would you migrate a live table off numbered columns without downtime?Create the child table with its constraints, backfill by unpivoting non-null slots while preserving slot order as an ordinal, then dual-write so both shapes stay current. Move readers across one at a time and reconcile counts, and only drop the numbered columns once no reader remains. Throughout, forbid new code from touching the old columns so the problem stops growing.
saying these in an interview costs you the question
- Answering only 'it breaks first normal form' with no operational consequence
- Proposing a comma-separated string or array column as the fix without discussing constraints and lookup cost
- Claiming a single index can serve an OR across three separate columns
- Adding a fourth numbered column as the solution to running out of slots
- Treating latitude/longitude or first/last name as the same anti-pattern