Production-grade database schema for a Shopify-like e-commerce platform featuring multi-store management, product catalog with variants and images, inventory tracking, order lifecycle management, fulfillment, payment processing, and discount engine.
Nothing is sold at the product level. products holds the title, the vendor and the SEO fields, but price, sku and weight live on product_variants — and orders, inventory and images all point at the variant. A product with no variants cannot be bought, which is why real stores create a default variant even for a single-option item.
The order is written as a copy, not as a set of links. order_items carries title, variant_title, sku and price alongside variant_id, so a receipt printed a year later still reads the way it did at checkout even if the product was renamed, repriced or removed. The foreign key is there to answer what this was, not to supply the display text.
Fulfilment is its own two-level structure rather than a status on the order. fulfillments is one shipment with its tracking number, and fulfillment_items says which order lines, and how many of each, went into that box — which is the only way a partial shipment has somewhere to live.
A normalised design would give each variant a set of named option values. Three fixed columns are blunt, but they turn the lookup for Large / Blue into a single indexed predicate rather than a group-by over a join, and three axes cover essentially every physical product. The cost is real: a fourth axis means a schema change, and the meaning of each slot lives in the product, not in the column name.
One stock number cannot tell the difference between sold and held in someone's cart. Splitting them lets checkout move units into reserved immediately and into a sale on payment, so two customers cannot both buy the last unit — and an abandoned cart simply releases reserved instead of needing a compensating correction.
status, financial_status and fulfillment_status run on different clocks. An order can be paid and unshipped, shipped and refunded, or cancelled after both. Folding them into one enum produces a combinatorial list that always misses a case; keeping them apart means each question is answered by whichever subsystem actually knows the answer.
shops.owner_id makes a shop belong to exactly one owner account, and users exist independently so one person can own several shops. The alternative — a shop_id on users — would stop a merchant from running two stores without a second login, which is the common case rather than the edge case.