E-commerce data model

This example models an online store from browsing to delivery: customers and their saved addresses, a product catalog organized in a category tree, shopping carts, orders with their lines, payments and shipments.

#Design notes

• Orders keep the price that was charged. order_line.unit_price and the totals on the order are stored, not looked up from the product, so later price changes don't rewrite history.
• Carts and orders are separate. A cart is temporary and can belong to a guest; an order is a permanent record that always has a customer and a shipping address.
• Categories form a tree through a relationship from category to itself (parent_category_id).
• Payments are attempts. An order can have several, including failed ones, so the relationship is many-to-one. Card numbers stay with the payment provider; only its reference is stored.
• Shipments are separate from orders, so one order can ship in several parcels.
• Status, payment method, country and currency are reference lists with their values, which become lookup tables and foreign keys in SQL.

To extend it: add product_variant for sizes and colors, review for product ratings, promotion and coupon for discounts, or return_request for returns.

#Diagram

E-commerce Store: 10 entities, 4 reference lists and 12 relationships. Each box is an entity with its attributes (PK marks the primary key); solid lines are relationships in crow's foot notation, dashed lines point to reference lists.

Open this model in EntityModel.com to explore it, change it and export it. Nothing is uploaded: the model travels in the link. Or download model.json.

#Entities

#Attributes

#Address

A shipping or billing address saved by a customer.

#Cart

A shopping cart, before checkout.

#Cart Item

A product and quantity in a cart.

#Category

A node in the product category tree, such as Shoes > Running.

#Customer

A person with an account on the store.

#Order

A placed order. Totals are stored as charged, so later price changes do not alter history.

#Order Line

One product on an order, with the price charged.

#Payment

A payment attempt against an order. Card details are held by the payment provider, not here.

#Product

An item for sale. Prices are per unit in the store currency.

#Shipment

A parcel sent for an order. Large orders can ship in several parcels.

#Country

Countries, identified by their ISO 3166-1 alpha-2 code.

#Currency

Currencies, identified by their ISO 4217 code.

#Order Status

The stages an order moves through.

#Payment Method

Ways a customer can pay.

#Relationships

#Reference data

#Country

6 values · Source: ISO 3166-1 (excerpt)

#Currency

5 values · Source: ISO 4217 (excerpt)

#Order Status

6 values · Source: Example values

#Payment Method

4 values · Source: Example values

#SQL DDL (PostgreSQL)

Generated from the model. In the app, Export → Code also writes MySQL, Snowflake and Databricks DDL, DBML, dbt schema.yml and data contracts.

-- E-commerce Store — data model
-- PostgreSQL DDL, generated 2026-11-03
-- Made with EntityModel.com

-- Reference list: Order Status
CREATE TABLE order_status (
  status_code TEXT NOT NULL,
  status_name TEXT NOT NULL,
  PRIMARY KEY (status_code)
);
COMMENT ON TABLE order_status IS 'The stages an order moves through.';

-- Reference list: Payment Method
CREATE TABLE payment_method (
  method_code TEXT NOT NULL,
  method_name TEXT NOT NULL,
  PRIMARY KEY (method_code)
);
COMMENT ON TABLE payment_method IS 'Ways a customer can pay.';

-- Reference list: Country
CREATE TABLE country (
  country_code TEXT NOT NULL,
  country_name TEXT NOT NULL,
  PRIMARY KEY (country_code)
);
COMMENT ON TABLE country IS 'Countries, identified by their ISO 3166-1 alpha-2 code.';
COMMENT ON COLUMN country.country_code IS 'ISO 3166-1 alpha-2 code.';
COMMENT ON COLUMN country.country_name IS 'English short name.';

-- Reference list: Currency
CREATE TABLE currency (
  currency_code TEXT NOT NULL,
  currency_name TEXT NOT NULL,
  PRIMARY KEY (currency_code)
);
COMMENT ON TABLE currency IS 'Currencies, identified by their ISO 4217 code.';
COMMENT ON COLUMN currency.currency_code IS 'ISO 4217 code.';

CREATE TABLE customer (
  customer_id BIGINT NOT NULL,
  email TEXT NOT NULL,
  full_name TEXT NOT NULL,
  phone TEXT,
  created_at TIMESTAMP NOT NULL,
  marketing_opt_in BOOLEAN NOT NULL,
  PRIMARY KEY (customer_id)
);
COMMENT ON TABLE customer IS 'A person with an account on the store.';
COMMENT ON COLUMN customer.customer_id IS 'Surrogate key.';
COMMENT ON COLUMN customer.email IS 'Login and contact email.';
COMMENT ON COLUMN customer.created_at IS 'When the account was created.';
COMMENT ON COLUMN customer.marketing_opt_in IS 'Consent to marketing email.';

CREATE TABLE address (
  address_id BIGINT NOT NULL,
  customer_id BIGINT NOT NULL,
  address_line TEXT NOT NULL,
  city TEXT NOT NULL,
  postal_code TEXT NOT NULL,
  country_code TEXT NOT NULL,
  is_default BOOLEAN NOT NULL,
  PRIMARY KEY (address_id)
);
COMMENT ON TABLE address IS 'A shipping or billing address saved by a customer.';
COMMENT ON COLUMN address.customer_id IS 'The customer who saved the address.';
COMMENT ON COLUMN address.address_line IS 'Street and number.';
COMMENT ON COLUMN address.is_default IS 'The customer''s default shipping address.';

CREATE TABLE category (
  category_id INTEGER NOT NULL,
  category_name TEXT NOT NULL,
  parent_category_id INTEGER,
  PRIMARY KEY (category_id)
);
COMMENT ON TABLE category IS 'A node in the product category tree, such as Shoes > Running.';
COMMENT ON COLUMN category.parent_category_id IS 'The parent category; empty for top-level categories.';

CREATE TABLE product (
  product_id BIGINT NOT NULL,
  sku TEXT NOT NULL,
  product_name TEXT NOT NULL,
  description TEXT,
  category_id INTEGER NOT NULL,
  unit_price NUMERIC(12,2) NOT NULL,
  currency_code TEXT NOT NULL,
  stock_quantity INTEGER NOT NULL,
  is_active BOOLEAN NOT NULL,
  PRIMARY KEY (product_id)
);
COMMENT ON TABLE product IS 'An item for sale. Prices are per unit in the store currency.';
COMMENT ON COLUMN product.sku IS 'Stock keeping unit.';
COMMENT ON COLUMN product.description IS 'Long description shown on the product page.';
COMMENT ON COLUMN product.unit_price IS 'Current list price.';
COMMENT ON COLUMN product.stock_quantity IS 'Units available to sell.';
COMMENT ON COLUMN product.is_active IS 'Shown in the store.';

CREATE TABLE cart (
  cart_id BIGINT NOT NULL,
  customer_id BIGINT,
  created_at TIMESTAMP NOT NULL,
  PRIMARY KEY (cart_id)
);
COMMENT ON TABLE cart IS 'A shopping cart, before checkout.';
COMMENT ON COLUMN cart.customer_id IS 'Empty for guest carts.';

CREATE TABLE cart_item (
  cart_item_id BIGINT NOT NULL,
  cart_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INTEGER NOT NULL,
  PRIMARY KEY (cart_item_id)
);
COMMENT ON TABLE cart_item IS 'A product and quantity in a cart.';

CREATE TABLE customer_order (
  order_id BIGINT NOT NULL,
  order_number TEXT NOT NULL,
  customer_id BIGINT NOT NULL,
  shipping_address_id BIGINT NOT NULL,
  order_status_code TEXT NOT NULL,
  ordered_at TIMESTAMP NOT NULL,
  currency_code TEXT NOT NULL,
  subtotal_amount NUMERIC(12,2) NOT NULL,
  shipping_amount NUMERIC(12,2) NOT NULL,
  tax_amount NUMERIC(12,2) NOT NULL,
  total_amount NUMERIC(12,2) NOT NULL,
  PRIMARY KEY (order_id)
);
COMMENT ON TABLE customer_order IS 'A placed order. Totals are stored as charged, so later price changes do not alter history.';
COMMENT ON COLUMN customer_order.order_number IS 'Number shown to the customer.';
COMMENT ON COLUMN customer_order.subtotal_amount IS 'Sum of the order lines.';
COMMENT ON COLUMN customer_order.total_amount IS 'Amount charged.';

CREATE TABLE order_line (
  order_line_id BIGINT NOT NULL,
  order_id BIGINT NOT NULL,
  product_id BIGINT NOT NULL,
  quantity INTEGER NOT NULL,
  unit_price NUMERIC(12,2) NOT NULL,
  PRIMARY KEY (order_line_id)
);
COMMENT ON TABLE order_line IS 'One product on an order, with the price charged.';
COMMENT ON COLUMN order_line.unit_price IS 'Price at the time of the order.';

CREATE TABLE payment (
  payment_id BIGINT NOT NULL,
  order_id BIGINT NOT NULL,
  payment_method_code TEXT NOT NULL,
  provider_reference TEXT,
  amount NUMERIC(12,2) NOT NULL,
  paid_at TIMESTAMP,
  PRIMARY KEY (payment_id)
);
COMMENT ON TABLE payment IS 'A payment attempt against an order. Card details are held by the payment provider, not here.';
COMMENT ON COLUMN payment.provider_reference IS 'The payment provider''s transaction ID.';
COMMENT ON COLUMN payment.paid_at IS 'Empty until the payment succeeds.';

CREATE TABLE shipment (
  shipment_id BIGINT NOT NULL,
  order_id BIGINT NOT NULL,
  carrier TEXT NOT NULL,
  tracking_number TEXT,
  shipped_at TIMESTAMP,
  delivered_at TIMESTAMP,
  PRIMARY KEY (shipment_id)
);
COMMENT ON TABLE shipment IS 'A parcel sent for an order. Large orders can ship in several parcels.';

-- Keys between tables
ALTER TABLE customer ADD CONSTRAINT uq_customer_email UNIQUE (email);
ALTER TABLE product ADD CONSTRAINT uq_product_sku UNIQUE (sku);
ALTER TABLE customer_order ADD CONSTRAINT uq_customer_order_order_number UNIQUE (order_number);
ALTER TABLE address ADD CONSTRAINT fk_address_country_code FOREIGN KEY (country_code) REFERENCES country (country_code);
ALTER TABLE product ADD CONSTRAINT fk_product_currency_code FOREIGN KEY (currency_code) REFERENCES currency (currency_code);
ALTER TABLE customer_order ADD CONSTRAINT fk_customer_order_order_status_code FOREIGN KEY (order_status_code) REFERENCES order_status (status_code);
ALTER TABLE customer_order ADD CONSTRAINT fk_customer_order_currency_code FOREIGN KEY (currency_code) REFERENCES currency (currency_code);
ALTER TABLE payment ADD CONSTRAINT fk_payment_payment_method_code FOREIGN KEY (payment_method_code) REFERENCES payment_method (method_code);
ALTER TABLE address ADD CONSTRAINT fk_saved_by FOREIGN KEY (customer_id) REFERENCES customer (customer_id);
ALTER TABLE category ADD CONSTRAINT fk_subcategory_of FOREIGN KEY (parent_category_id) REFERENCES category (category_id);
ALTER TABLE product ADD CONSTRAINT fk_listed_in FOREIGN KEY (category_id) REFERENCES category (category_id);
ALTER TABLE cart ADD CONSTRAINT fk_belongs_to_customer FOREIGN KEY (customer_id) REFERENCES customer (customer_id);
ALTER TABLE cart_item ADD CONSTRAINT fk_item_in FOREIGN KEY (cart_id) REFERENCES cart (cart_id);
ALTER TABLE cart_item ADD CONSTRAINT fk_item_product FOREIGN KEY (product_id) REFERENCES product (product_id);
ALTER TABLE customer_order ADD CONSTRAINT fk_placed_by FOREIGN KEY (customer_id) REFERENCES customer (customer_id);
ALTER TABLE customer_order ADD CONSTRAINT fk_ships_to FOREIGN KEY (shipping_address_id) REFERENCES address (address_id);
ALTER TABLE order_line ADD CONSTRAINT fk_line_of FOREIGN KEY (order_id) REFERENCES customer_order (order_id);
ALTER TABLE order_line ADD CONSTRAINT fk_line_product FOREIGN KEY (product_id) REFERENCES product (product_id);
ALTER TABLE payment ADD CONSTRAINT fk_pays_for FOREIGN KEY (order_id) REFERENCES customer_order (order_id);
ALTER TABLE shipment ADD CONSTRAINT fk_ships FOREIGN KEY (order_id) REFERENCES customer_order (order_id);

-- Reference values
INSERT INTO order_status (status_code, status_name) VALUES
  ('PENDING', 'Pending payment'),
  ('PAID', 'Paid'),
  ('SHIPPED', 'Shipped'),
  ('DELIVERED', 'Delivered'),
  ('CANCELLED', 'Cancelled'),
  ('REFUNDED', 'Refunded');

INSERT INTO payment_method (method_code, method_name) VALUES
  ('CARD', 'Credit or debit card'),
  ('PAYPAL', 'PayPal'),
  ('APPLE_PAY', 'Apple Pay'),
  ('GIFT_CARD', 'Gift card');

INSERT INTO country (country_code, country_name) VALUES
  ('US', 'United States'),
  ('CA', 'Canada'),
  ('GB', 'United Kingdom'),
  ('DE', 'Germany'),
  ('FR', 'France'),
  ('AU', 'Australia');

INSERT INTO currency (currency_code, currency_name) VALUES
  ('USD', 'US Dollar'),
  ('CAD', 'Canadian Dollar'),
  ('GBP', 'Pound Sterling'),
  ('EUR', 'Euro'),
  ('AUD', 'Australian Dollar');

#Mermaid

Paste into a mermaid code block on GitHub, GitLab or Notion to show the diagram there.

---
title: E-commerce Store
---
%% Made with EntityModel.com
erDiagram
  customer {
    bigint customer_id PK "Surrogate key."
    string email "Login and contact email."
    string full_name "Full Name"
    string phone "Phone"
    timestamp created_at "When the account was created."
    boolean marketing_opt_in "Consent to marketing email."
  }
  address {
    bigint address_id PK "Address ID"
    bigint customer_id FK "The customer who saved the address."
    string address_line "Street and number."
    string city "City"
    string postal_code "Postal Code"
    string country_code FK "Country Code"
    boolean is_default "The customer's default shipping address."
  }
  category {
    int category_id PK "Category ID"
    string category_name "Category Name"
    int parent_category_id FK "The parent category; empty for top-level categories."
  }
  product {
    bigint product_id PK "Product ID"
    string sku "Stock keeping unit."
    string product_name "Product Name"
    string description "Long description shown on the product page."
    int category_id FK "Category ID"
    decimal unit_price "Current list price."
    string currency_code FK "Currency Code"
    int stock_quantity "Units available to sell."
    boolean is_active "Shown in the store."
  }
  cart {
    bigint cart_id PK "Cart ID"
    bigint customer_id FK "Empty for guest carts."
    timestamp created_at "Created At"
  }
  cart_item {
    bigint cart_item_id PK "Cart Item ID"
    bigint cart_id FK "Cart ID"
    bigint product_id FK "Product ID"
    int quantity "Quantity"
  }
  customer_order {
    bigint order_id PK "Order ID"
    string order_number "Number shown to the customer."
    bigint customer_id FK "Customer ID"
    bigint shipping_address_id FK "Shipping Address ID"
    string order_status_code FK "Order Status Code"
    timestamp ordered_at "Ordered At"
    string currency_code FK "Currency Code"
    decimal subtotal_amount "Sum of the order lines."
    decimal shipping_amount "Shipping Amount"
    decimal tax_amount "Tax Amount"
    decimal total_amount "Amount charged."
  }
  order_line {
    bigint order_line_id PK "Order Line ID"
    bigint order_id FK "Order ID"
    bigint product_id FK "Product ID"
    int quantity "Quantity"
    decimal unit_price "Price at the time of the order."
  }
  payment {
    bigint payment_id PK "Payment ID"
    bigint order_id FK "Order ID"
    string payment_method_code FK "Payment Method Code"
    string provider_reference "The payment provider's transaction ID."
    decimal amount "Amount"
    timestamp paid_at "Empty until the payment succeeds."
  }
  shipment {
    bigint shipment_id PK "Shipment ID"
    bigint order_id FK "Order ID"
    string carrier "Carrier"
    string tracking_number "Tracking Number"
    timestamp shipped_at "Shipped At"
    timestamp delivered_at "Delivered At"
  }
  order_status {
    string status_code PK "Status Code"
    string status_name "Status Name"
  }
  payment_method {
    string method_code PK "Method Code"
    string method_name "Method Name"
  }
  country {
    string country_code PK "ISO 3166-1 alpha-2 code."
    string country_name "English short name."
  }
  currency {
    string currency_code PK "ISO 4217 code."
    string currency_name "Currency Name"
  }
  address }o--|| customer : "Saved By"
  category }o--|| category : "Subcategory Of"
  product }o--|| category : "Listed In"
  cart }o--|| customer : "Belongs To"
  cart_item }o--|| cart : "Item In"
  cart_item }o--|| product : "Is For"
  customer_order }o--|| customer : "Placed By"
  customer_order }o--|| address : "Ships To"
  order_line }o--|| customer_order : "Line Of"
  order_line }o--|| product : "Orders"
  payment }o--|| customer_order : "Pays For"
  shipment }o--|| customer_order : "Ships"
  address }o..|| country : "Country Code"
  product }o..|| currency : "Currency Code"
  customer_order }o..|| order_status : "Order Status Code"
  customer_order }o..|| currency : "Currency Code"
  payment }o..|| payment_method : "Payment Method Code"

#Frequently asked questions

#What tables does an e-commerce database need?

At minimum: customer, address, product, category, order (often named customer_order, since ORDER is a reserved word), order line, payment and shipment, plus lookup tables for order status and payment method. A cart and cart item support saved baskets before checkout.

#Why is the order table called customer_order?

ORDER is a reserved word in SQL, so naming the table customer_order avoids quoting it in every query. The display name is still Order.

#How do I store prices that change over time?

Store the price actually charged on each order line. For a full price history of the catalog, add a product_price table with effective-from and effective-to dates.