Designing a Database Structure for an Ecommerce Platform
A production-grade schema for products, variants, orders, and carts — and the one rule that prevents the worst ecommerce data bugs.
Building an ecommerce site is a genuinely different scope of project than a portfolio or blog — payments, inventory, and order state all have to be right, not just "working in the demo." Here's the realistic build order.
Before any code: how many products, do you need variants (size/color), physical or digital goods, one seller or a marketplace? These answers change the database design significantly, so get them settled before modeling anything.
At minimum: products, product_variants (if applicable), categories, customers, orders, order_items, carts/cart_items. Get this right early — retrofitting variants or multi-currency support onto a live store with real orders is painful.
Decide early: cart tied to a session (works for guests) or to a logged-in user (persists across devices) — most real stores support both, merging a guest cart into the user's cart on login.
Use a payment provider (Stripe, PayPal) rather than ever touching raw card numbers yourself — PCI compliance for handling cards directly is not something a typical project should take on. The real complexity is in your own code: reserving inventory during checkout, handling a payment that succeeds but where your server crashes before recording the order, and webhook handling for asynchronous payment confirmation.
A real order isn't just "placed" — it moves through states: pending → paid → processing → shipped → delivered, with cancelled and refunded as branches. Model this as an explicit status field with a defined set of valid transitions, not a loose string anyone can set to anything.
Decrement stock at the moment of a CONFIRMED payment, not at "add to cart" — otherwise an abandoned cart holds stock hostage indefinitely. For high-traffic stores, this needs care around race conditions (two people buying the last item simultaneously).
Whoever runs the store needs to see orders, update statuses, and manage inventory without touching the database directly — this is usually a bigger chunk of real work than the storefront itself.
Payment processing, tax calculation for multiple jurisdictions, and shipping rate calculation are all solved problems with mature third-party services — building these from scratch is rarely a good use of a small team's time.
A production-grade schema for products, variants, orders, and carts — and the one rule that prevents the worst ecommerce data bugs.
Cache-aside, TTLs, invalidation, and using Redis for rate limiting and sessions — practical caching patterns that hold up in production.
The real trade-offs between REST and GraphQL — caching, over/under-fetching, the N+1 problem, and when each one is genuinely the better choice.