E-commerce Database Schema database schema

What this schema models

A production-grade e-commerce database schema you can copy, fork and ship. 36 tables, 12 enums and 46 relationships covering the full online-store lifecycle: customer accounts and address books, a brand and category catalog, product options that generate SKU-level variants, multi-warehouse inventory, cart, checkout, orders, partial shipments, RMA returns, refunds, payments, discount codes, gift cards, and shipping and tax configuration. WHAT MAKES THIS DIFFERENT FROM A TOY EXAMPLE Most e-commerce ER diagrams you find online model products and orders and stop. This one models the parts that actually break in production: - Product variants done properly. products is the marketing item; product_variants is the sellable, stockable unit that carries the SKU and price. product_options and product_option_values define the axes (Size, Colour), and variant_option_values pins each variant to exactly one value per axis, so "Medium + Blue" resolves to a single row. Carts, orders, wishlists and stock all reference the variant, never the product. - Order snapshots. order_lines copy sku, title and unit price rather than joining live, and order_addresses freeze the shipping address at checkout. Renaming a product or editing your address book can never rewrite what an old invoice says was bought. - Partial fulfilment. shipments and shipment_lines let one order ship as several parcels from several warehouses, with shipped quantities summing to at most the ordered quantity. - Inventory as a ledger. inventory_movements is append-only with a signed quantity, so inventory_items.on_hand is a cached running total that can always be rebuilt and audited. - Money as integers. Every amount is stored in minor units. Tax is basis points, so 8.25 percent is 825. No floats anywhere. - Refunds as their own rows, never subtracted from the original payment. Gift cards use the same signed-ledger shape as stock. WHAT YOU GET Clean DBML that imports straight into DB Planner, plus a laid-out board where cards are colour-coded by zone — shopper, catalog, variants, inventory, cart, orders, fulfilment, reverse flow, money, promotions and configuration — with sticky notes explaining the design decision behind each group. Every table carries a Note explaining why it exists. Postgres-flavoured types (uuid, timestamptz, char(3) currency codes) that translate cleanly to MySQL or SQL Server. WHO IT IS FOR Developers designing an online store backend, students who need a realistic e-commerce ER diagram for coursework, teams migrating off Shopify or WooCommerce to something custom, and anyone who wants a reference data model to argue with before writing their first migration.

Tables (36)

Relationships (46)

• cart_items.cart_id references carts.id many to one
• cart_items.variant_id references product_variants.id many to one
• carts.customer_id references customers.id many to one
• categories.parent_id references categories.id many to one
• customer_addresses.customer_id references customers.id many to one
• discount_redemptions.customer_id references customers.id many to one
• discount_redemptions.discount_id references discounts.id many to one
• discount_redemptions.order_id references orders.id many to one
• gift_card_transactions.gift_card_id references gift_cards.id many to one
• gift_card_transactions.order_id references orders.id many to one
• gift_cards.issued_to_customer_id references customers.id many to one
• inventory_items.variant_id references product_variants.id many to one
• inventory_items.warehouse_id references warehouses.id many to one
• inventory_movements.inventory_item_id references inventory_items.id many to one
• order_addresses.order_id references orders.id many to one
• order_lines.order_id references orders.id many to one
• order_lines.variant_id references product_variants.id many to one
• orders.customer_id references customers.id many to one
• payment_methods.customer_id references customers.id many to one
• payments.order_id references orders.id many to one
• payments.payment_method_id references payment_methods.id many to one
• product_categories.category_id references categories.id many to one
• product_categories.product_id references products.id many to one
• product_images.product_id references products.id many to one
• product_images.variant_id references product_variants.id many to one
• product_option_values.option_id references product_options.id many to one
• product_options.product_id references products.id many to one
• product_reviews.customer_id references customers.id many to one
• product_reviews.product_id references products.id many to one
• product_variants.product_id references products.id many to one
• products.brand_id references brands.id many to one
• refunds.payment_id references payments.id many to one
• refunds.return_id references returns.id many to one
• return_lines.order_line_id references order_lines.id many to one
• return_lines.return_id references returns.id many to one
• returns.order_id references orders.id many to one
• shipment_lines.order_line_id references order_lines.id many to one
• shipment_lines.shipment_id references shipments.id many to one
• shipments.order_id references orders.id many to one
• shipments.warehouse_id references warehouses.id many to one
• shipping_rates.zone_id references shipping_zones.id many to one
• variant_option_values.option_value_id references product_option_values.id many to one
• variant_option_values.variant_id references product_variants.id many to one
• wishlist_items.variant_id references product_variants.id many to one
• wishlist_items.wishlist_id references wishlists.id many to one
• wishlists.customer_id references customers.id many to one

How to use this template

1. Open the diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy.
3. Export it as PostgreSQL, MySQL, or MSSQL DDL, or as C# models.

Frequently asked questions

What is in the E-commerce Database Schema schema?

36 tables and 46 relationships, across 236 columns. The table names and the full relationship list are shown on this page.

Can I export it to PostgreSQL or MySQL?

Yes. Every DB Planner template exports as DBML, PostgreSQL, MySQL, or MSSQL DDL, and as C# models.

Is this template free?

Yes. You can open it in the browser without an account, and copy it into your own workspace with a free DB Planner account.

Can I change it after copying it?

Yes. A copy is yours: you can rename tables, add columns, and export it. Changes the author makes later do not affect your copy.