A customer table has the columns phone1, phone2 and phone3. Why is that treated as a repeating group and a first normal form violation, and what does the corrected design look like?

middleshould knowfreq 48%

answer

columns differing only by a number
three is an arbitrary guess
OR across columns, three indexes
most cells NULL
home_phone vs mobile_phone = real attributes

basics

~20 s

It encodes a one-to-many relationship horizontally: the limit of three is arbitrary, most cells are NULL, a fourth phone needs DDL and code changes, and any search must check all three columns. Fix: a child table with one row per phone plus a foreign key.

solid answer

~60 s

Each cell does hold one value, which is why people defend the design, but the column set is the collection — phone1..phone3 is a list laid out sideways. That breaks 1NF's no-repeating-groups rule and creates concrete problems:

Arbitrary cardinality. Three is a guess. The customer with four phones forces ALTER TABLE plus changes in every query, DTO and form.
Sparse and wasteful. Most customers have one phone, so two columns are NULL on nearly every row.
No single place to constrain. Uniqueness of a phone across a customer needs a cross-column check; a NOT NULL cannot express "at least one".
Every query triples. "Who has phone 555-0100?" becomes an OR across three columns, needing three indexes and defeating a simple one.
Position becomes meaningless metadata. Is phone2 the mobile or just the second one entered? Nothing says.

The fix: customer_phone(id, customer_id, phone, kind, position) with a foreign key. Cardinality becomes unlimited, one index answers the lookup, kind captures the meaning the column number was pretending to carry, and adding a phone is an INSERT.

Each cell does hold one value, which is why people defend the design, but the *column set* is the collection — `phone1..phone3` is a list laid out sideways. That breaks 1NF's no-repeating-groups rule and creates concrete problems: - **Arbitrary cardinality.** Three is a guess. The customer with four phones forces `ALTER TABLE` plus changes in every query, DTO and form. - **Sparse and wasteful.** Most customers have one phone, so two columns are NULL on nearly every row. - **No single place to constrain.** Uniqueness of a phone across a customer needs a cross-column check; a `NOT NULL` cannot express "at least one". - **Every query triples.** "Who has phone 555-0100?" becomes an OR across three columns, needing three indexes and defeating a simple one. - **Position becomes meaningless metadata.** Is `phone2` the mobile or just the second one entered? Nothing says. The fix: `customer_phone(id, customer_id, phone, kind, position)` with a foreign key. Cardinality becomes unlimited, one index answers the lookup, `kind` captures the meaning the column number was pretending to carry, and adding a phone is an `INSERT`.

code

sql · 9 lines

sql

-- repeating group: incomplete the moment phone4 is added
SELECT * FROM customer
WHERE phone1 = '555-0100' OR phone2 = '555-0100' OR phone3 = '555-0100';

-- child table: one index, correct for any number of phones
SELECT c.*
FROM customer c
JOIN customer_phone p ON p.customer_id = c.id
WHERE p.phone = '555-0100';

go deeper

for a junior

Say that the numbered columns are a list in disguise, that three is arbitrary, and that the fix is a separate phones table with a foreign key.

for a middle

Add the query and index consequences of OR-across-columns, sparsity from NULLs, and the constraints that only become expressible in a child table.

for a senior

Point out that adding a fourth column silently makes existing queries incomplete, describe the unpivot-and-dual-write migration, and give the naming heuristic that separates repeating groups from genuinely distinct attributes.

for a principal

Discuss when a fixed-arity flat design is a deliberate read-model choice over a normalized source of truth, and how you keep such decisions explicit rather than accidental.

The disguise

This violation is harder to spot than a comma-separated string because each individual cell looks perfectly atomic. phone2 = '555-0100' is one value of one domain. The violation lives one level up: the schema uses column position to encode which element of a collection you are looking at. In relational terms, attributes must be distinct properties of the entity; here three attributes are the same property repeated, which is the classic definition of a repeating group.

The same shape appears everywhere once you learn to see it: address_line1..4, item1_sku/item1_qty/item2_sku/item2_qty, q1_sales, q2_sales, q3_sales, q4_sales, child1_name, child2_name, approver_a, approver_b.

The costs, concretely

The arity is a guess that will be wrong. Someone chose three. The business will eventually have a customer with four phone numbers, and the fix is an ALTER TABLE ADD COLUMN phone4 plus changes to every SELECT listing columns, every insert, every mapping class, every form and every report. The child table version absorbs the same requirement with an INSERT and no deployment.

Sparsity. If the median customer has one phone, two thirds of the cells are NULL. Beyond the storage waste, it means every predicate must reason about NULLs, and "how many phones does this customer have" is a CASE-counting expression instead of COUNT(*).

Constraints cannot be expressed naturally. You want: a phone is unique within a customer; a customer has at least one phone; exactly one phone is primary. In the repeating-group design these become multi-column CHECK expressions that grow quadratically with the arity, if they are expressible at all. In the child table: a unique constraint on (customer_id, phone), an application or deferred-constraint rule for at-least-one, and a partial unique index on (customer_id) WHERE is_primary for exactly-one-primary.

Queries and indexes multiply. "Find the customer with this number" is WHERE phone1 = ? OR phone2 = ? OR phone3 = ?. To index it you need three separate indexes and the optimizer must union them; adding phone4 silently makes existing queries incomplete — they still run, they just miss rows, which is the worst kind of bug. Against customer_phone, one index on phone answers it and stays correct forever.

Semantics smuggled into position. What distinguishes phone1 from phone2? Often nobody knows: sometimes it means home/work/mobile, sometimes it is simply insertion order, and different parts of the codebase assume different things. Making that a real column (kind, position) forces the meaning to be stated and lets it be constrained.

Aggregation is awkward. Anything of the form "per phone" — counting, grouping by area code, joining to a call log — requires unpivoting three columns into rows before you can start.

The corrected design

customer(id, name, …)
customer_phone(
  id, customer_id -> customer(id),
  phone, kind, position,
  UNIQUE (customer_id, phone))

Properties you gain: unlimited cardinality without DDL; one index for reverse lookup; a natural COUNT(*); per-phone attributes (verified flag, verified_at, do-not-call) that had nowhere to live before; cascade delete; and an explicit kind/position instead of folklore about column numbers.

When the flat design is still the right call

Be pragmatic, and be precise about why:

The cardinality is fixed by the domain, not by convenience, and each slot is genuinely a distinct attribute. home_phone and mobile_phone are two different properties of a customer, not two elements of a list — that is not a repeating group, and naming them properly is the tell. latitude/longitude and start_date/end_date are the same story.
A denormalized fixed set of columns is a deliberate reporting or read-model choice, kept alongside a normalized source of truth.

The heuristic: if the column names differ only by a number, it is a repeating group. If they differ by meaning, they are separate attributes and 1NF has nothing to complain about.

Migrating

Create the child table, backfill by unpivoting the non-null values (preserving which column each came from as position or kind), dual-write while readers move over, verify, then drop the columns in a later change. Expect the backfill to surface duplicates and junk values that the three-column design was quietly tolerating.

## The disguise This violation is harder to spot than a comma-separated string because each individual cell looks perfectly atomic. `phone2 = '555-0100'` is one value of one domain. The violation lives one level up: the schema uses *column position* to encode which element of a collection you are looking at. In relational terms, attributes must be distinct properties of the entity; here three attributes are the same property repeated, which is the classic definition of a repeating group. The same shape appears everywhere once you learn to see it: `address_line1..4`, `item1_sku/item1_qty/item2_sku/item2_qty`, `q1_sales, q2_sales, q3_sales, q4_sales`, `child1_name, child2_name`, `approver_a, approver_b`. ## The costs, concretely **The arity is a guess that will be wrong.** Someone chose three. The business will eventually have a customer with four phone numbers, and the fix is an `ALTER TABLE ADD COLUMN phone4` plus changes to every `SELECT` listing columns, every insert, every mapping class, every form and every report. The child table version absorbs the same requirement with an `INSERT` and no deployment. **Sparsity.** If the median customer has one phone, two thirds of the cells are NULL. Beyond the storage waste, it means every predicate must reason about NULLs, and "how many phones does this customer have" is a `CASE`-counting expression instead of `COUNT(*)`. **Constraints cannot be expressed naturally.** You want: a phone is unique within a customer; a customer has at least one phone; exactly one phone is primary. In the repeating-group design these become multi-column `CHECK` expressions that grow quadratically with the arity, if they are expressible at all. In the child table: a unique constraint on `(customer_id, phone)`, an application or deferred-constraint rule for at-least-one, and a partial unique index on `(customer_id) WHERE is_primary` for exactly-one-primary. **Queries and indexes multiply.** "Find the customer with this number" is `WHERE phone1 = ? OR phone2 = ? OR phone3 = ?`. To index it you need three separate indexes and the optimizer must union them; adding `phone4` silently makes existing queries incomplete — they still run, they just miss rows, which is the worst kind of bug. Against `customer_phone`, one index on `phone` answers it and stays correct forever. **Semantics smuggled into position.** What distinguishes `phone1` from `phone2`? Often nobody knows: sometimes it means home/work/mobile, sometimes it is simply insertion order, and different parts of the codebase assume different things. Making that a real column (`kind`, `position`) forces the meaning to be stated and lets it be constrained. **Aggregation is awkward.** Anything of the form "per phone" — counting, grouping by area code, joining to a call log — requires unpivoting three columns into rows before you can start. ## The corrected design ``` customer(id, name, …) customer_phone( id, customer_id -> customer(id), phone, kind, position, UNIQUE (customer_id, phone)) ``` Properties you gain: unlimited cardinality without DDL; one index for reverse lookup; a natural `COUNT(*)`; per-phone attributes (verified flag, verified_at, do-not-call) that had nowhere to live before; cascade delete; and an explicit `kind`/`position` instead of folklore about column numbers. ## When the flat design is still the right call Be pragmatic, and be precise about *why*: - The cardinality is **fixed by the domain, not by convenience**, and each slot is genuinely a distinct attribute. `home_phone` and `mobile_phone` are two different properties of a customer, not two elements of a list — that is not a repeating group, and naming them properly is the tell. `latitude`/`longitude` and `start_date`/`end_date` are the same story. - A denormalized fixed set of columns is a deliberate reporting or read-model choice, kept alongside a normalized source of truth. The heuristic: **if the column names differ only by a number, it is a repeating group.** If they differ by meaning, they are separate attributes and 1NF has nothing to complain about. ## Migrating Create the child table, backfill by unpivoting the non-null values (preserving which column each came from as `position` or `kind`), dual-write while readers move over, verify, then drop the columns in a later change. Expect the backfill to surface duplicates and junk values that the three-column design was quietly tolerating.

Are latitude and longitude columns a repeating group?
No. They differ by meaning, not by an index number — each is a distinct attribute of the same point, and neither is an element of an open-ended collection. The tell for a repeating group is column names that differ only by a numeric suffix and hold interchangeable values of the same property, such as phone1 and phone2.
What integrity rules become expressible once phones live in a child table?
Uniqueness of a phone within a customer becomes a single unique constraint on the pair of columns, and cascade delete removes phones with the customer automatically. Exactly-one-primary can be enforced with a partial unique index on the customer id restricted to primary rows. Per-phone attributes such as verified status also finally have somewhere to live.

It is a coat rack with exactly three hooks bolted to the wall: fine until someone arrives with a fourth coat, and you cannot count coats without checking each hook individually.

saying these in an interview costs you the question

Defending phone1/phone2/phone3 because each cell holds a single value, missing that the column set is the collection
Solving the fourth-phone problem by adding phone4 rather than questioning the shape
Not realising that existing OR-across-columns queries silently become incomplete when a column is added
Calling every fixed set of related columns a repeating group, including genuinely distinct attributes like start_date and end_date

A customer table has the columns phone1, phone2 and phone3. Why is that treated as a repeating group and a first normal form violation, and what does the corrected design look like?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~3 min

The disguise

The costs, concretely

The corrected design

When the flat design is still the right call

Migrating

▾common follow-ups2 questions

▾analogy~10 s

▾red flags4 flags

§ context

▾related questions6 questions

▾related topics4 topics

The disguise

The costs, concretely

The corrected design

When the flat design is still the right call

Migrating

answer

go deeper

context