Normalize vs denormalize

The decision

To normalize means to store each fact in exactly one place. Other tables point to it by an id, and a join combines the tables when you read. To denormalize means to copy a fact into more than one place on purpose, so that a read touches fewer rows. Normalizing makes writes simple and correct. Denormalizing makes reads fast, but copies can disagree. You choose by counting reads and writes.

Choose normalize when

• The fact changes often, such as stock, price or a user's address. One change must fix every screen at once.
• The data is about money or rights. A wrong copy costs real rupees.
• You do not know all the queries yet. Clean tables answer questions you did not plan for.
• A query reads only a handful of rows, so the join is cheap.

Choose denormalize when

• A query reads too many rows and you counted them. Count first, then copy.
• The fact is a snapshot: the value at one moment, never updated. The price paid on an order line is one. A snapshot needs no repair.
• The fact changes rarely, but it is read very often.
• Your database has no joins (many NoSQL stores). Then you keep one table for each query.

The numbers that change the answer

Assumption: one write costs 5 times one row read, because a write also updates indexes and logs.

A copy that you must keep fresh is a live copy. Each change to the fact must update every copy. Use this rule for a live copy:

• break-even reads between changes = 5 x copies written per change / rows saved per read

If real reads between changes are far above break-even, copy. If they are near or below it, join.

Worked example 1 (Assumption: an order list screen). The screen shows 20 orders. With clean tables it reads 20 orders, 20 restaurants and 60 items (3 per order): 100 rows. With the restaurant name and the total copied into the order row it reads 20 rows. At 3,000 views per second (Assumption) that saves 80 x 3,000 = 240,000 row reads per second. Both copies are written once, when the order is made, and never change. They add a few bytes to each row and no repair work.

Worked example 2: copy a shop name. The name sits in 40,000 order rows (Assumption). The shop is renamed once in 30 days. Break-even = 5 x 40,000 / 1 = 200,000 reads between renames. Assumption: 50 order views per second for this shop. Real reads = 50 x 86,400 x 30 = 129,600,000. That is 129,600,000 / 200,000 = 648 times break-even. Copy it. The space is 40,000 x 50 bytes = 2 MB.

Worked example 3: do not copy stock. The stock count would sit in 5,000 carts (Assumption). It changes every 10 seconds. Break-even = 5 x 5,000 / 1 = 25,000 reads between changes. Assumption: each cart is viewed every 300 seconds, which is 5,000 / 300 = 16.7 views per second. Reads between changes = 16.7 x 10 = 167. That is far below 25,000, and the copy costs 5,000 / 10 = 500 writes per second. Join instead.

What breaks with each choice

• Normalized: reads slow down as joins touch more rows. Joins also become slow or impossible across servers when you split the data. Fix: count rows, add an index (a sorted list that helps the database find rows), then copy only the fact that hurts.
• Denormalized: a write to one copy succeeds and another fails, so two screens show two answers. Fix: name one source of truth and write all copies from one piece of code. Use a transaction (a group of changes that all happen or none happen) when the copies share a database. Run a repair job that compares copies with the source.

What it costs

• Adding a copy later is cheap. Add a column and fill it. Assumption: 100,000,000 rows at 5,000 rows per second takes 100,000,000 / 5,000 = 20,000 seconds, which is 5.6 hours, in the background.
• Merging copies back is harder, because you must find which copy is right. So normalize first and copy later.

How each level answers

SDE-1: Normalizing stores each fact once. Denormalizing copies a fact to make reads faster, and the price is that copies can disagree.

SDE-2: I keep orders and payments normalized, because they are money. I copy the restaurant name and the total into the order row as snapshots. The order list is read all day, and old orders must keep old values.

Senior: I compare reads between changes with 5 x copies. The shop name has 648 times the break-even, so I copy it. Stock has 167 reads against 25,000, so I join. The failure is a copy that drifts after a partial write. So I write the copy in the same transaction, and a nightly job compares copies with the source.

Staff: Each copy is a long-term cost, so I want an owner for the code that writes it. I normalize first and copy only after a measured slow query, so we can reverse the choice. The migration is add a column, fill it in the background, switch reads, then watch. I measure rows read per second and the count of drifted copies the repair job finds. I also track p99 (the time within which 99 of 100 requests finish) of the screen. If drift is not near zero, the copy was a mistake.

Say it in the interview

"I keep the money tables normalized, so each fact has one source of truth. For the order list, I copy the restaurant name and total into the order row as snapshots, which cuts 100 row reads to 20 and never needs repair. I would not copy fast-changing data like stock, because the copy would need hundreds of writes per second."

Common mistakes

• Denormalizing before measuring. It is wrong because you pay the cost of copies, and a missing index may have been the real problem.
• Copying a live fact into many rows without a repair job. It is wrong because one failed write leaves the data wrong, with no alarm.
• Normalizing a receipt. A receipt must keep the old price and name. If it joins to the live row, a price change rewrites history.

Sources

• Apache Cassandra, data modelling introduction (copy data so that each query reads one table; no joins): https://cassandra.apache.org/doc/latest/cassandra/developing/data-modeling/intro.html (opens in a new tab) (checked 26 Oct 2026).
• The write cost of 5 times a read, all row counts, view rates and sizes are assumptions made for teaching. The arithmetic is calculated from them.