Skip to content
Optimal Lab

System design · interactive

Data modeling: normalize or denormalize

A blog's feed shows each post with its author's name and its comment count. Store each fact once and every feed read has to join and count. Copy the name and the count onto the post and the feed is one scan, but every rename and every comment has to update the copies. Count the rows under three workloads.

rows touched per 100 operations · 100 reads per write

  • Normalized5942
  • Denormalized992
  • Denormalized, updated async992

solid: reading · faded: writing

schema · normalized

usersidname
postsidauthor_idtitle
commentsidpost_idbody

Normalized, 100 reads per write: 5942 rows per 100 operations (5941 reading, 1 writing). Stale for: never.

Design

1

List the entities and the questions

Users write posts; readers comment. The hot question is the feed: the newest posts with who wrote them and how many comments each has.

3 users · 10 posts · 40 comments · feed of 10

2

Normalize: each fact in one place

users(name), posts(author_id), comments(post_id). A rename is one row. A comment is one row. Nothing can disagree with itself.

rename: 1 row · comment: 1 row

3

Pay for it on every read

The feed joins each post to its author and counts its comments. Rows touched grow with comments per post, on the most-read page of the site.

  • 10 posts
  • join 10 user rows
  • count 40 comment rows

= 60 rows per feed read

4

Denormalize: copy what the read needs onto the row

posts gains author_name and comment_count. The feed is 10 rows. The copies have to be kept right: in the same transaction, or by a job that runs later.

  • Post a comment: insert 1 comment
  • Rename an author: update 1 user row

Nothing to keep in sync. The feed is slower, and the comment count is computed on every read.

5

Decide by the read/write ratio

Copies pay off when reads far outnumber writes to the copied fact, which is most feeds. With 3 posts per author a rename is cheap; with 10,000 it isn't. Keep the source normalized and treat the copy as a cache you can rebuild.

Workload

rows/100 opsstale forNormalized5942neverDenormalized992neverDenormalized, updated async992seconds

In production

Tools and technologies

Keeping copies in sync is the hard part; these are the usual ways to do it.

Not tied to one language — use these names when you search docs or design a system.