Answers · Where the data lives

Why is it actually bad to store the same value in two different tables?

In shortBecause once two copies disagree, nothing in the database says which one is correct, and fixing it needs information you no longer have.

It is not mainly wasted disk. The cost is that correcting one fact becomes an update that has to reach every copy, and a missed copy leaves two contradictory values with no tiebreaker. Storage is cheap; a table that disagrees with itself is not.

The update that reaches one copy

Take customer 4471, who has 312 order rows, each carrying the region north. The customer moves to west. If an update runs over all 312 rows, the table is consistent and right, though it has touched 312 rows to record one fact about one customer. Now suppose a second update runs with a date filter nobody remembered. It touches 266 rows and leaves 46. The table now says north on 46 rows and west on 266. Nothing in the schema records which of those is current truth. A report grouping orders by region runs without error and attributes 46 orders to the wrong region. The dangerous step is not the stale table, which is uniformly wrong and can be fixed by one correction. The dangerous step is the disagreement: the repair now needs information from outside the database.

q = 1 - (1 - e)^n

At a per-row failure chance e = 0.002 and n = 5 copies, (0.998)^5 = 0.99003992, so q = 0.00996008, about one chance in a hundred. At n = 400, (0.998)^400 = 0.44897, so q = 0.55103, better than even. No amount of care brings e to zero, so the only term the schema controls is n. That is why this is a design problem and not an operations problem: removing the copies removes the exponent, while discipline only shifts it.

The natural guess is that the copies are just bytes and the only cost is disk. That guess would be right if the copies were independent, but they are not. They are claims about one fact. When they disagree, the database has no column saying which claim is newer. The same is true across two tables: if region lives in customers and also in orders, updating customers and forgetting orders leaves the same contradiction. A constraint could force equality only if every copy were updated together, which is the very operation that failed. So duplication does not merely waste space; it creates a state, two values for one fact, that the schema permits and cannot resolve.

The row that cannot exist

The same defect shows from another side: a fact can only exist where a row exists to carry it. If customer 4471 has not ordered yet, there is no order row and therefore nowhere to put the customer. The usual workaround is an order row with empty order fields, which means every order query must remember to exclude rows that are not orders, and the ones that forget are wrong by a small amount. Deleting the last order of a customer deletes the customer, not because anyone chose a cascade, but because the only place the customer existed was inside that row. A tidy-up of orders older than seven years silently removes every customer who has not ordered since, and no constraint is violated.

FIG 1Six rows of the merged table, with the dependency visible

	customer_id	order_id	amount	region code
row 1	4471	9001	180	1
row 2	4471	9002	240	1
row 3	4471	9003	95	1
row 4	5520	9004	310	2
row 5	5520	9005	150	2
row 6	4471	9006	85	3

The key is order_id. amount varies with order_id, which is right. region code is the same on rows 1, 2 and 3 because they share customer 4471, so it depends on customer_id, which is not the key. Row 6 shares customer 4471 and carries region code 3, a contradiction the table cannot detect.

The question to ask of every column is what it depends on. amount depends on order_id, the key, so it belongs. region code depends on customer_id, which is not the key, so it is in the wrong place. The repair is mechanical: customer(customer_id, region code) and order(order_id, customer_id, amount). Now one correction updates one customer row, and the orders do not carry a second copy at all. The insert and delete failures go with it: a customer can exist without an order, and deleting an order cannot delete the customer.

What normal form actually says

Every non-key column must depend on the key, the whole key, and nothing but the key. Depend on the key rules out a column that is not a fact about this subject, which is the customer name stored on an order. The whole key rules out a column that depends on part of a compound key, such as product name in a table keyed by order and product. Nothing but the key rules out a column that depends on another non-key column, which is region depending on customer. Three clauses, three failures, one repair: find the dependency that does not point at the key, and give it a table whose key it does point at.

Where this stops being true: duplication can be a decision rather than an accident, for a read model, a snapshot, or a cached total. Then the copy is not an independent claim about the present; it is a claim about a particular time or a derived value. It is safe only if the system makes disagreement impossible or detectable: one writer, generated columns, a constraint, or an as-of timestamp. The moment a second copy can be edited by an ordinary update and no rule forces the copies to agree, the argument returns in full. If the fact never changes, the update anomaly disappears, but the insert and delete anomalies remain because the fact still depends on a row existing for something else.

Worth keeping

A fact stored twice can be corrected once, and the table then has no way to say which copy is right.
Duplication removes rows you wanted to keep and prevents rows you wanted to add, because a fact can only exist where a row exists to carry it.
A column that depends on something other than the key should be moved to a table keyed by that something.

Where this goes next

This question is one step of Designing a Table to Live With, a course that works through its subject from the beginning. The page that teaches this part of it is The Same Fact in Two Places, and it reads the whole way through whether or not you have an account.

What an account changes is the practice: problems standing under every idea, marked with a reason rather than a tick, in a copy of the course that is yours.

Read that page The whole course