Database Design for Google Calendar: A Tutorial

Original Article ↗ Hacker News Discussion ↗

Designing a database for a Google Calendar–style app exposes surprisingly complex issues around data modeling, recurrence rules, and time zones. Commenters largely favor a carefully normalized relational schema—often with events stored with time zone context rather than naïve UTC—arguing that NoSQL document stores become rigid and difficult to query as relationships and access patterns grow. Many highlight the real-world pitfalls of recurring events, daylight saving changes, and future time zone rule shifts, stressing the need for robust libraries and upfront logical modeling over ad hoc schema evolution.

Overall reception of the tutorial

  • Many found it a clear, concrete guide to modeling a non-trivial domain.
  • The step of defining attributes as questions is widely praised for surfacing ambiguity and aligning with customer needs.
  • Some note that modeling like this is rarely taught well, and they see it as valuable for both juniors and experienced developers.

“Anchor” terminology and modeling concepts

  • Several commenters initially find “anchor” a strange term but end up appreciating it because it avoids overloaded terms like “entity” or “aggregate root.”
  • Examples of what is and isn’t an anchor are seen as more helpful than abstract definitions.
  • There is agreement that logical modeling (first) is database-independent and should precede any physical schema work.

Recurring events, edits, and complexity of time

  • Recurrence and “what am I doing today?” queries are described as surprisingly hard, especially with exceptions and edits.
  • Some advocate generating concrete instances for each recurrence to better handle per-occurrence exceptions; others prefer rule-based storage for efficiency.
  • Time zones and daylight savings are viewed as a major source of complexity.
    • One camp: store a timezone with each date/time; UTC alone is not enough for future events.
    • Another camp: store in UTC and convert, but acknowledge that future rules can change.
  • Real-world edge cases (ships crossing zones, overlapping jurisdictions with different DST rules) are cited as particularly nasty.

SQL vs NoSQL for calendars

  • Strong sentiment that a calendar’s highly relational nature (users, events, time zones, related events) fits relational databases better.
  • Criticism of document stores for poor joins and rigidity in evolving access patterns; suggested pattern: start with SQL, move to NoSQL only when access patterns and scale demand it.
  • Counterpoint: “NoSQL” is not monolithic; some systems have strong indexing, transactions, and joins. Many large sites use NoSQL selectively.
  • Note that Google Calendar is reportedly backed by a Spanner-like system, blurring SQL/NoSQL boundaries.

iCalendar as storage vs interchange

  • Some propose using iCalendar blobs plus fast scanning instead of a schema.
  • Others argue iCalendar is an interchange format from the 1990s, not ideal as a primary storage model, and lacks normalized structures and stable identifiers.

Difficulty and interviewing

  • Multiple commenters describe calendar/booking and datetime questions as deceptively hard.
  • Consensus: use solid libraries; don’t reimplement time, time zones, or recurrence logic from scratch.