Section 5: The Truth Is a Ledger - Data Modeling
Executive Summary: This section builds the data model that every later section stands on. We start where Staff candidates always start — with the access patterns, not the tables — then derive the likes ledger: one row per (post, user) relationship, the system's single source of truth. The section's centerpiece is a quiet piece of leverage: the ledger's uniqueness constraint is the idempotency, turning double-taps into no-ops at the storage engine with zero application logic. We place the counter row, choose the two indexes, defend hard delete for unlike, and establish the rule the whole module orbits: the counter is derived, never authoritative.
The interviewer taps the whiteboard where your API from Section 4 ends and asks the question that sinks the Dismissal archetype on contact: "So where does a like actually live?" The candidate who answered with UPDATE posts SET like_count = like_count + 1 has one integer column — and an integer cannot answer "did Priya like this post?", cannot make her unlike remove exactly her like, and cannot tell her second tap from her first. Before drawing a single table, resist the urge to answer with storage at all. Answer with questions the storage must serve.
Access Patterns First, Tables Second
The house rule for any data-modeling phase: enumerate the queries, then shape the data so each query is cheap. For the like counter, five patterns cover the product:
| # | Access pattern | When it runs | Shape it demands |
|---|---|---|---|
| 1 | Did this viewer like post X? | Every post render — it draws the heart | Point read by (post, user) |
| 2 | What is post X's count? | Every post render — the hottest read in the system | Point read by post |
| 3 | Set / unset a like | Every tap — the write path from Section 4 | Conditional write by (post, user) |
| 4 | Who liked post X? | Deferred to Section 11 — but shaped now | Paginated scan within one post |
| 5 | Which posts did I like? | Profile and history surfaces | Lookup by user across posts |
Patterns 1 and 2 ride the feed-render hot path — ~116K reads/s average, ~350K/s at peak — so both must be point reads. Pattern 3 must be idempotent, because Section 4 promised a PUT that is safe to retry blindly. Patterns 4 and 5 are not launch features, but a model that welds those doors shut fails the follow-up round before it begins.
Sign in to continue reading
The rest of this lesson is available with a free account. Signing in with Google or Microsoft is free.
Sign in to read the full lesson