Section 5: A Rule, Its Exceptions, and Everyone's Copy - Data Modeling
Executive Summary: Under Section 4's contract sit seven entities, all partitioned by calendar_id and co-located. A recurring series is one rule row plus its exceptions, never instances, and an index on the series' end time keeps a week view to roughly 50 rows. We pick a Spanner-class store over default last-write-wins stores and a single relational primary, and defend copying every event onto every guest's calendar.
The interviewer points at Section 4's expand=true: "So where do the instances come from? What's actually in the table?"
"Before any schema: who reads this data, and how often?"
Access Patterns First
| Access pattern | Rate (ledger) | Shape it demands |
|---|---|---|
| Week view of one calendar | ~70K/s peak, p99 ≤ 200 ms | Range by time within one calendar |
| Sync delta since a token | ~350K/s peak, ~80% "no changes", p99 ≤ 50 ms | Ordered log per calendar, read from a position |
| Get one event | Per open/edit | Point lookup by (calendar, event) |
| Free/busy for N calendars | ~3.5K/s peak × ~6 calendars ≈ 21K window reads/s | Same range as the week view, intervals only |
| Reminders due in the next 48 h | ~1B/day | Instances in a near-future window, fed by changes |
| Room overlap check | p99 ≤ 2 s, never double-booked | Serializable read-then-write in one room |
Every row starts with "one calendar". That's the partition key announcing itself.
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