Question 6
Normalized vs denormalized
In simple terms, what does normalized mean for a database? What does denormalized mean? Give a couple of real-world examples.
Answer outline
Normalized means each fact lives in one logical place, and related rows link to it by ID. You don't copy the same data onto many rows, so when something changes you update it once.
Denormalized means you copy data or store precomputed totals on purpose, usually to make reads faster or queries simpler. The price is that you must keep the copies in sync or accept stale values.
Three examples show the tradeoff:
- 1.Normalized author:
Userhas anidand aname, andPosthas anauthorIdthat points to it. The name lives on one user row and is never repeated on a post. - 2.Denormalized comment count: a
Postrow storescommentCountso the feed shows counts without runningCOUNT(*)over comments for every row. It's faster, but you must increment and decrement that field whenever comments change, or recompute it periodically. - 3.Denormalized author name: caching
authorNameon eachPostavoids a join per row in a read-heavy feed. If the user changes their name, you must update many posts or tolerate an out-of-date name until the next refresh.
Principles
- Normalized data keeps one place to update each fact, so writes stay simple and consistent.
- Denormalized data trades write complexity for read speed, and every copy needs an explicit update rule.
- A stale copy is a bug you chose, so write down who updates each denormalized field and when.
The same post, first linked to the author by ID and then with the name copied onto the row:
Sketch: normalized vs duplicated
// Normalized: one source for the name
User(id: "u1", name: "Alex")
Post(id: "p1", authorId: "u1", text: "Hello")
// Denormalized for read speed: name copied onto the post
Post(id: "p1", authorId: "u1", authorName: "Alex", text: "Hello")
Follow-up angles
- Most teams start normalized and add a denormalized copy only after measuring the read that hurts.
