PostgreSQL

E-commerce database schema

A practical e-commerce schema with products, variants (size/color), inventory per SKU, cart and order tables, and payment + shipment records. Designed for a single-merchant store with normalized variants.

Start for free

Database schema previewE-commerce database schema

Recreate this in Raltey

Copy this prompt

Paste this into Raltey's AI agent to generate the same diagram, then tweak it on the canvas.

Prompt

Design a PostgreSQL database schema for: E-commerce database schema.

A practical e-commerce schema with products, variants (size/color), inventory per SKU, cart and order tables, and payment + shipment records. Designed for a single-merchant store with normalized variants.

Include these tables: product, variant, warehouse, inventory, customer, cart, cart_item, order, order_item, payment, shipment.

Use proper primary keys, foreign keys, and indexes. Export SQL DDL for PostgreSQL.

Context

- Single merchant, multiple warehouses.
- Variants modeled as their own table (SKU-level).
- Stock is tracked per (variant, warehouse) pair.
- Order and cart are kept separate - cart is mutable, order is immutable.

Patterns

- Order is an immutable snapshot - copy name/price into order_item, never derive from variant at read time.
- Inventory uses a composite PK (variant_id, warehouse_id) - cheap upserts.
- Money in integer cents.

Tables

The schema, table by table

product

DBML - product

Table product {
  id          uuid pk
  slug        text unique not null
  name        text not null
  description text
  is_active   boolean default true
}

variant

A purchasable SKU under a product.

DBML - variant

Table variant {
  id          uuid pk
  product_id  uuid [ref: > product.id, not null]
  sku         text unique not null
  price       int not null   // cents
  attributes  jsonb           // {size: "L", color: "blue"}
}

warehouse

DBML - warehouse

Table warehouse {
  id uuid pk
  name text not null
  country_code text not null
}

inventory

On-hand stock per (variant, warehouse).

DBML - inventory

Table inventory {
  variant_id   uuid [ref: > variant.id]
  warehouse_id uuid [ref: > warehouse.id]
  quantity     int not null default 0
  pk (variant_id, warehouse_id)
}

customer

DBML - customer

Table customer {
  id uuid pk
  email text unique not null
  created_at timestamptz default `now()`
}

cart

DBML - cart

Table cart {
  id          uuid pk
  customer_id uuid [ref: > customer.id]
  created_at  timestamptz default `now()`
  expires_at  timestamptz
}

cart_item

DBML - cart_item

Table cart_item {
  cart_id    uuid [ref: > cart.id]
  variant_id uuid [ref: > variant.id]
  quantity   int not null
  pk (cart_id, variant_id)
}

order

Snapshot of the cart at purchase time.

DBML - order

Table order {
  id          uuid pk
  customer_id uuid [ref: > customer.id, not null]
  status      text not null  // pending, paid, shipped, delivered, refunded
  subtotal    int not null
  shipping    int not null
  tax         int not null
  total       int not null
  placed_at   timestamptz default `now()`
}

order_item

DBML - order_item

Table order_item {
  id         uuid pk
  order_id   uuid [ref: > order.id, not null]
  variant_id uuid [ref: > variant.id, not null]
  name       text not null  // captured at purchase time
  unit_price int not null
  quantity   int not null
}

payment

DBML - payment

Table payment {
  id         uuid pk
  order_id   uuid [ref: > order.id, not null]
  provider   text not null   // stripe, paypal
  external_id text unique
  amount     int not null
  status     text not null
}

shipment

DBML - shipment

Table shipment {
  id         uuid pk
  order_id   uuid [ref: > order.id, not null]
  carrier    text
  tracking   text
  status     text not null  // ready, in_transit, delivered
}

Relationships

From	To	Type
product	variant	1:N
variant	inventory	1:N
warehouse	inventory	1:N
customer	cart	1:N
customer	order	1:N
cart	cart_item	1:N
order	order_item	1:N
order	payment	1:N
order	shipment	1:N

Notes from the field

Never let a foreign key from order_item.variant_id imply current price - copy at order time.
Add a partial index on order(status) where status in ('pending', 'paid') for fulfilment dashboards.

Frequently asked

Should I model variants as a separate table?

Yes for any store with multiple SKUs per product (sizes, colors). It keeps pricing, stock, and SKU codes at the right grain. Embedding variants inside product as JSON only works for ultra-small catalogs.

How do I prevent inventory going negative?

Use a CHECK constraint (quantity >= 0) and update via a single UPDATE ... WHERE quantity >= :n statement so the row lock holds across the check. Optionally introduce a reservations table for high-traffic stores.

More templates

FlowchartSaaS onboarding email flow FlowchartCustomer support runbook flow FlowchartPayment webhook retry and error flow FlowchartGitHub PR review and merge flow FlowchartLogin with magic link flow ArchitectureThree-tier SaaS architecture on AWS ArchitectureMicroservices architecture on Kubernetes ArchitectureReal-time data pipeline on GCP

Open this template in Raltey

Edit it on the canvas, ask the AI agent to extend it, then share or export.