Pick the System of Record From Access Patterns and Transactions
Choosing a system of record starts with correctness requirements and access patterns, not with the most scalable database. This interactive lesson compares relational, document, wide-column, key-value, and NewSQL options for storefront checkout. Covers: Checkout invariants, multi-entity transactions, conditional updates, uniqueness constraints, write contention, separate catalog read scaling, Amazon Aurora, Azure SQL Database, Cloud SQL. Prerequisites: Relational and NoSQL database basics.
We'll cover the following...
In a data-design round, the interviewer isn't scoring product recall. They're checking whether we choose storage based on correctness requirements, access patterns, and failure boundaries. When the interviewer says, “Use the most scalable database,” we should test that claim against the Seasonal storefront’s two different workloads.
The catalog supports many reads of product descriptions, images, and displayed availability. A briefly stale catalog page may be acceptable. Checkout creates an order, reserves inventory, records a payment attempt, and changes order state. Those writes need a system of record, the durable authority whose accepted state determines what the business considers true.
The checkout requirements are specific before we name a product:
Prevent two accepted orders from consuming the same remaining inventory
Associate each successful payment attempt with ...