Normalization versus denormalization

01One fact or many copies?

An author changes her displayed name from Mira to Mira Chen. Suppose that name was copied into every post row. Updating only some posts leaves several different “current” names for the same author.

Normalization reduces that repeated-current-data problem:

1. Store the author's current name once in an Author record.
2. Store authorId in each Post record.
3. When displaying posts, look up the referenced Author record or join it with the posts.

A join combines related records while reading. This layout avoids rewriting every post after a name change, but adds lookup work to reads.

Denormalization deliberately saves a copy or precomputed result to make a particular read cheaper. A feed can copy the display name beside each entry, avoiding repeated author lookups. The design must then explain how those copies are updated, how old they may be and how to repair them. The copied name is not a second independent way to change the author's current name.

02Not all duplication has the same meaning

Use Author(authorId,currentName,version) and Post(postId,authorId,text). Distinguish three meanings that may initially contain the same text:

A materialized view is a saved result of a query, maintained or refreshed so later reads avoid recomputing it. Its existence does not prove it is current.

Plan each copied field:

1. Identify its source. The Author record accepts current-name changes.
2. State its meaning. Is the feed displaying the current name, or the name at publication?
3. Carry a source version. A copied current name can remember which author version it has applied.
4. Define freshness and rebuilding. Decide how long copies may lag and how to recreate them from source records.
5. Include deletion. A privacy deletion must cover copied names too; stopping future updates does not erase existing views.

Historical values are separate facts. An order's agreed price must not change when today's catalog price changes.

03Worked example: author names in a feed

Author 17 is the author being renamed. Her current name is Mira at version 4; the edit saves Mira Chen at version 5. She appears in 100,000 feed entries. A background updater safely changes 1,000 entries/s, so one ideal sweep takes 100 seconds, before failures and concurrent work.

1. Save Author version 5 and pending update work together. A crash must not make the system forget that copied names need refreshing.
2. A worker sends or reads the author/version change. Delivery can repeat or arrive out of order.
3. For each current-name feed row, compare versions before changing it. In one conditional row update, replace both name and applied version only if incoming 5 is greater than the version stored in that row. A separate comparison followed by an unguarded write could still let an older worker overwrite a newer name.
4. A delayed version 4 arrives. The row already remembers 5, so the worker skips the older update instead of restoring Mira.
5. A new post is inserted during the sweep. The post creator copies the name and version it read, and saves a pending version-check job with the new feed row. That job reads the Author record and conditionally refreshes this row if it copied an older version. This covers inserts missed by the original sweep; merely scanning a snapshot of old rows would not.
6. Some rows fail. The updater records completed page ranges, such as the last processed post ID, and leaves failed ranges pending. It retries those ranges with the same conditional version update. Repeating a successful update cannot move it backward. Monitor remaining age and rebuild mismatched rows from Author records if necessary.

If every read requires the immediately current name, this delayed sweep cannot meet that promise. A current Author lookup or coordinated publication may be simpler than changing 100,000 copies as though they changed at one instant. One name edit causing many updates is update fanout; count it before choosing the faster read.

04Optimize measured reads and preserve meaning

Choose copies only for measured read work:

• Try an index or bounded join first when it meets the response target with simpler maintenance.
• Copy only fields the read needs. Duplicating an entire record adds more update and deletion work than copying one display field.
• Name current and historical data differently. A current-name copy and an order-time snapshot need different update rules.
• Count the full cost: saved read lookups, copied bytes, updates after one source change, new rows during updates and recovery after failure.
• Respect retention and privacy. Legitimate history still has a storage and deletion policy.

In the interview, trace one read and one edit. Then stop the updater halfway and insert a new post. Explain which displayed values can be old, how those specific rows are found again and what makes repeated repairs safe.