System Design Beginner

01  Data Modeling & Storage

A schema is a bet on your access patterns. Model the entities first, then let how the data is actually read decide the keys, the indexes, and ultimately which kind of store it lives in.

Step 1 / 2

Design the relational data model for the checkout side of an e-commerce site: customers place orders for products. Give the tables with primary and foreign keys, explain the relationship between orders and products, and explain why the price the customer paid must NOT live only in the products table.

Hints
  • Start from the nouns: customer, product, order. Which relationship is many-to-many?
  • A many-to-many relationship needs a join table. What does one row of that table represent?
  • Prices change. What should an old order show after the merchant raises a price?
Your design is reviewed against a hidden rubric.