Design an Inventory Service: SQL Schema, Safe Stock Reservations and Analytics

Design an inventory service. The prompt is deliberately vague: you decide which functional and non-functional requirements the service must meet. The discussion centers on the relational data model, so expect most of the time to go into designing the SQL tables. After the design, the interviewer asks what interesting analytics the stored data makes possible; that part is free-form. ### Clarifying Questions - Whose inventory is this: a retailer with warehouses and stores, a marketplace with many sellers, or something else? - Does the service only track stock counts, or does it also reserve stock for orders in progress and release it when an order is abandoned? - Which operations must be strongly consistent (for example, never selling more units than exist), and which can be eventually consistent? - Is stock tracked only per location, or also per batch with expiry dates, or per serial number? ### Part 1 — Requirements Define the functional and non-functional requirements you will design for, with a rough scale. ```hint Let the data model drive the scope Pick requirements that each imply a table, a constraint or an index. That keeps the rest of the discussion concrete. ``` #### What This Part Should Cover - Core operations: receiving stock, reserving and releasing it, fulfilling orders, adjusting counts, and querying availability. - Consistency, availability and latency targets, with the reasoning behind them. - An estimate of products, locations and write rates. ### Part 2 — SQL data model Design the tables, with their columns, keys, constraints and indexes, and show how the core operations read and write them. ```hint Current state or history? Consider whether you store only current quantities, only a log of movements, or both, and what each choice does to correctness under concurrency and to auditing. ``` #### Clarifying Questions for this Part - Must every change to stock be auditable after the fact? - Can two orders race for the last unit of the same product at the same location? #### What This Part Should Cover - Normalized core tables with primary keys, foreign keys and check constraints that protect the invariants. - A concurrency-safe way to reserve stock. - Indexes justified by the queries they serve. ### Part 3 — Analytics on the inventory data Describe the interesting analytics you could compute from the data you now store, and what you would need to add to support them. ```hint Mine the movement history A record of every stock change, with its reason and time, answers questions that a table of current quantities cannot. ``` #### What This Part Should Cover - Several concrete analyses, each with the business question it answers. - The query or pipeline shape for at least one of them. - Keeping analytical load away from the transactional database. ### What a Strong Answer Covers - Requirements chosen deliberately and stated before the schema, since the prompt leaves them open. - A schema whose constraints make overselling and lost updates impossible, not merely unlikely. - A clear trade-off discussion between a current-state table and a movement ledger. - Analytics tied to the stored data, with transactional and analytical workloads kept apart. ### Follow-up Questions - How would you shard the inventory tables if one database could no longer handle the write rate, and what happens to queries across locations? - How would you handle a flash sale in which thousands of requests target the same product at once? - How do you reconcile the database with a physical count that disagrees with it? - How would you serve availability to a storefront that needs low-latency reads for millions of products?