Normalization vs Denormalization

Your data model has two extremes. Fully normalized: every fact lives in exactly one place, connected by foreign keys. Great for writes (update once, seen everywhere), bad for reads (join 5 tables to render one page). Fully denormalized: each row has everything needed to render a view. Great for reads (single fetch), bad for writes (update in 50 places when a user changes their name).

Every real system lives somewhere in between. Getting the balance right is the difference between a snappy app with cheap writes and a slow app with terrifying update cascades.

1. Read-heavy workload. Reads outnumber writes 10:1 or more → denormalize the hot read path.
2. Fixed access patterns. You always render "feed post with author." Then store "feed post with author" as a single row in the read store. Don't reinvent the join on every request.
3. Rarely-changing copied data. Username changes once a year; author_name copied to 10k post rows is fine. Don't copy data that changes every minute.
4. Data you can't afford to join across. Across shards, joins are application logic — either denormalize or fetch twice.
5. Wide-column stores demand it. Cassandra, DynamoDB explicitly recommend denormalization — they can't join, period.

• Update cascades. User changes their name → rewrite N rows. If N is big (celebrity with 10M posts), this is a background job, not a synchronous write.
• Potential inconsistency. Update the source, then update 10 copies. If one update fails, the system is briefly inconsistent. Idempotent retries + eventual consistency.
• Storage. Copying a 200-byte user record to 10M posts = 2GB of duplicate bytes. Usually fine; occasionally significant.
• Schema evolution. Renaming a column means rewriting the copies. Slower to change.

You want to show "posts by this user: 42" on their profile. Three options:

Compute from source: SELECT COUNT(*) FROM posts WHERE user_id = 5. Always right. Costs an index scan per profile view. OK at small scale; dies at scale.

Denormalized counter: users.post_count column, incremented on every post insert. One extra write per post, zero on read. Fast but drift-prone: failed increment → counter is off forever. Crashed replica → counter is off forever. Easy to corrupt.

Periodic reconciliation: keep the counter, but also run a nightly job UPDATE users SET post_count = (SELECT COUNT(*)...) . Counter drifts during the day; corrects at night. Best-of-both.

For financial data: never trust a denormalized counter. Reconcile from source every transaction. For social media likes: denormalized counter + occasional reconciliation is industry standard.

News feed denormalizes by fan-out on write. Reddit denormalizes comment counts onto posts. E-commerce denormalizes product name + price onto cart line items (price at time of purchase).