Design an Online Bookshop with a Focus on Payments and Database Design

Design an online bookshop. Customers browse and search a catalog of books, add books to a cart, check out and pay, and later look up their orders. The round concentrated on two areas, so expect most of the probing there: the payment flow, and the database design behind the catalog, stock, orders and payments. The report gives only the prompt and those two focus areas; settle the rest of the scope with the interviewer. ```hint Stock and money must agree Decide what happens between the moment a customer clicks "pay" and the moment a copy is committed to them, so that the last copy is never sold twice and nobody is charged for an order that does not exist. ``` ```hint Payment calls fail ambiguously A call to the payment provider can time out after the charge actually went through. Decide what lets you retry safely and how you find out what really happened. ``` ### Constraints and Clarifications - Not given: catalog size, traffic, whether the shop sells physical books, e-books or both, which payment methods it accepts, and whether it is a single store or a marketplace of sellers. Ask, or state your assumptions. ### Clarifying Questions - Is this one store selling its own stock, or a marketplace where independent sellers list books and must be paid out? - Physical books only, or also e-books that are delivered digitally and have no stock count? - Which payment methods are in scope (cards through a payment provider, gift cards, store credit), and is the card charged when the order is placed or when it ships? - Does adding a book to the cart reserve a copy, or is stock only checked at checkout? - How large is the catalog, how many orders arrive a day, and are there spikes such as a popular new release? - Are cancellations, refunds and partial shipments in scope? ### What a Strong Answer Covers - Settles the scope (single store or marketplace, physical or digital, payment methods) and sizes the browse and order paths before designing - A relational schema for books, stock, carts, orders, order items and payments, with keys, constraints and indexes justified by the queries they serve - An order state machine and a checkout that reserves stock and creates the order atomically, and never oversells under concurrent checkouts - A payment integration with idempotency keys, authorization and capture, provider webhooks, and reconciliation for ambiguous outcomes - Separation of the read-heavy catalog and search path from the transactional order path - Failure handling for payment timeouts, abandoned reservations and refunds, plus scaling and monitoring ### Follow-up Questions - Two customers check out the last copy of a book at the same moment. Walk through exactly what each database transaction does. - The payment provider times out and the customer clicks "pay" again. How do you guarantee a single charge? - The orders table has grown so large that order-history pages are slow. How do you partition or archive it without breaking those pages? - The shop becomes a marketplace and each seller must be paid out minus a commission. What changes in the payment and database design?