The short version
Every online store is the same 11 tables with different CSS. customers have addresses and carts; carts hold cart_items that reference products; products belong to categories and have product_images; orders snapshot cart items into order_items and collect payments; reviews link customers to products. This is the PostgreSQL schema behind Shopify, WooCommerce, and most custom storefronts.
The 11 tables at a glance
| Table | Purpose | Key FK |
|---|---|---|
customers | Who shops | — (core) |
addresses | Where to ship/bill | customer_id |
categories | Catalog taxonomy (self-referential) | parent_id → categories.id |
products | What you sell | category_id |
product_images | Product photos | product_id |
carts | Active shopping session | customer_id |
cart_items | Mutable cart lines | cart_id, product_id |
orders | Placed order (snapshot) | customer_id, shipping/billing address |
order_items | Immutable order lines | order_id, product_id |
payments | Payment attempt | order_id |
reviews | Post-purchase feedback | product_id, customer_id |
The three flows
Browse → cart: Customer browses products (joined to categories and product_images), adds to carts via cart_items. Cart is mutable — quantities change, items are removed.
Cart → order: At checkout, cart items are copied into order_items as an immutable snapshot. Even if the product price later changes, the order preserves what was actually purchased. The orders row records total, addresses, and timestamps (placed_at, paid_at, shipped_at).
Order → payment → review: payments records each charge attempt against the order. After fulfillment, reviews lets the customer rate the product.
-- Order history with items and payment status
select o.id, o.status, o.total_cents, p.status as payment_status,
oi.quantity, pr.title
from orders o
join order_items oi on oi.order_id = o.id
join products pr on pr.id = oi.product_id
left join payments p on p.order_id = o.id
where o.customer_id = $1
order by o.placed_at desc;Design decisions that matter
- Cart vs order: mutable vs immutable.
cart_itemscan change;order_itemsnever should. Copy, don't move, at checkout. - Addresses are separate from customers. One customer has many addresses (home, work). Orders reference specific address rows so the address at order time is preserved even if the customer later moves.
- Categories are self-referential.
categories.parent_id → categories.idbuilds a tree (Electronics → Phones → Cases) without a separate hierarchy table. - Prices in cents as integers. Avoid floating-point rounding.
price_cents integerwith acurrencycolumn is the safe default.
Explore it: the e-commerce schema diagram renders all 11 tables with foreign keys you can hover to trace — the same view you'd get introspecting a live store database.
FAQ
What tables does an e-commerce database need?
11 tables: customers, addresses, categories, products, product_images, carts, cart_items, orders, order_items, payments, and reviews.
What is the difference between cart_items and order_items?
Cart items are mutable until checkout. Order items are immutable snapshots copied at order time, preserving price and product as purchased.
Should I store prices as float or integer?
Integer cents. Float rounding corrupts money. Store price_cents integer and divide by 100 only at display time.
How do categories handle subcategories?
Self-referential foreign key: categories.parent_id → categories.id. Top-level categories have parent_id = NULL; children point at their parent.