A comprehensive e-commerce database schema designed for online stores and marketplaces. Includes product catalog with categories, customer management, order processing with line items, payment tracking, shipping addresses, and inventory management. Suitable for Shopify-like platforms, WooCommerce alternatives, or custom e-commerce solutions.
The centre of this schema is the line between the catalogue and the order. products, categories and product_images describe what is for sale right now and change whenever someone edits the shop. orders and order_items record what was actually bought, and must not change when the catalogue does.
That is why order_items carries product_name and unit_price of its own. It still points at the product for reporting, but the name and the price on the receipt are copies taken at checkout. Rename a product or run a sale and last month's orders stay readable.
Carts sit before that line and are deliberately loose. carts has both user_id and session_id so that someone can fill a basket before signing in, and expires_at so abandoned carts do not accumulate forever. Nothing in a cart is a promise — stock is not reserved and the price is read live from products.
The alternative is to join to products when rendering an old order, which means an invoice changes when the shop does — a product renamed, a price cut, a variant deleted. Copying costs two columns per line and makes the order self-contained. The foreign key stays for analytics, so nothing is lost by keeping both.
stock_quantity is a single number rather than a ledger of movements. It is the right call for a shop that is not also a warehouse: reads are constant-time and the decrement is one update. It stops being enough when you need to know why stock is what it is — returns, damage, receiving — at which point the column becomes a cached total over a movements table rather than the truth.
coupons.current_uses against max_uses is the simple form, and it is honest about its weakness: two checkouts at the same instant can both read the same count. If a coupon must never exceed its limit, the check belongs in a transaction that locks the coupon row, or usage becomes its own table with a unique constraint per user.
shipping_address_id references addresses, which means a customer editing their address rewrites history. This is the one place where the catalogue lesson has not been applied — for a shop that ships, copy the address onto the order the way the price is copied. It is left as a foreign key here because it is the common starting shape, and it is worth knowing it is the first thing to change.