Designing a Database Structure for an Ecommerce Platform

A well-designed database schema is the difference between an ecommerce platform that scales cleanly and one that needs a painful rewrite at 10x traffic. Here's a schema that holds up in the real world, not just in a tutorial demo.

Products and Variants

products
  id, name, description, category_id, base_price, created_at

product_variants
  id, product_id, sku, price, stock_quantity, attributes (json: {size: 'M', color: 'Blue'})

Never store price/stock directly on products if variants exist — a "T-Shirt" isn't purchasable, a "T-Shirt, Medium, Blue" is. This single decision avoids a painful migration later.

Customers and Addresses

customers
  id, email, password_hash, created_at

addresses
  id, customer_id, type (shipping/billing), line1, city, postal_code, country, is_default

Addresses live in their own table, not as columns on customers — a customer needs multiple addresses, and orders should snapshot the address used at time of purchase rather than reference it live (so a later address edit doesn't rewrite history).

Orders: The Table That Must Never Lie

orders
  id, customer_id, status, subtotal, tax, shipping, total, shipping_address (json snapshot), created_at

order_items
  id, order_id, product_variant_id, product_name (snapshot), unit_price (snapshot), quantity

Notice product_name and unit_price are snapshotted onto the order item, not just referenced via foreign key. If a product's name or price changes six months after an order, that historical order must still show what the customer actually paid — not today's price.

Cart

carts
  id, customer_id (nullable — guest carts), session_id, created_at

cart_items
  id, cart_id, product_variant_id, quantity

Indexes That Actually Matter

• products.category_id — every category page filters on this
• order_items.order_id and orders.customer_id — every "my orders" page and order-detail page hits these
• A unique index on product_variants.sku — SKU collisions corrupt inventory tracking silently

The One Rule That Prevents the Worst Bugs

Anything that represents "what happened" (an order, an invoice, a payment record) should snapshot the data it needs at that moment. Anything that represents "the current state of the world" (a product's current price, a customer's current address) can safely be a live reference. Confusing these two is the single most common root cause of ecommerce data bugs — a customer's invoice total quietly changing because a tax rate was updated after the fact.