E-commerce Database Schema Diagram
Every online store is the same 11 tables with different CSS. Customers have addresses and carts; carts hold cart items that reference products; products belong to categories and have images; orders snapshot cart items into order items and collect payments; reviews link customers to products. This is the complete e-commerce schema diagram that powers Shopify, WooCommerce, and most custom PostgreSQL storefronts.
Last updated: 2026-09-03
Tables in the E-commerce schema
| Column | Type | Nullable | Key |
|---|---|---|---|
| customers | |||
| id | uuid | No | PK |
| varchar | No | - | |
| first_name | varchar | Yes | - |
| last_name | varchar | Yes | - |
| phone | varchar | Yes | - |
| created_at | timestamptz | No | - |
| updated_at | timestamptz | Yes | - |
| addresses | |||
| id | uuid | No | PK |
| customer_id | uuid | No | - |
| label | varchar | Yes | - |
| line1 | varchar | No | - |
| line2 | varchar | Yes | - |
| city | varchar | No | - |
| state | varchar | Yes | - |
| postal_code | varchar | No | - |
| country | varchar(2) | No | - |
| is_default | bool | No | - |
| categories | |||
| id | uuid | No | PK |
| name | varchar | No | - |
| slug | varchar | No | - |
| parent_id | uuid | Yes | - |
| description | text | Yes | - |
| created_at | timestamptz | No | - |
| products | |||
| id | uuid | No | PK |
| title | varchar | No | - |
| slug | varchar | No | - |
| description | text | Yes | - |
| category_id | uuid | Yes | - |
| price_cents | integer | No | - |
| currency | varchar(3) | No | - |
| inventory_count | integer | No | - |
| is_active | bool | No | - |
| created_at | timestamptz | No | - |
| updated_at | timestamptz | Yes | - |
| product_images | |||
| id | uuid | No | PK |
| product_id | uuid | No | - |
| url | text | No | - |
| alt_text | varchar | Yes | - |
| position | integer | No | - |
| created_at | timestamptz | No | - |
| carts | |||
| id | uuid | No | PK |
| customer_id | uuid | Yes | - |
| status | varchar | No | - |
| created_at | timestamptz | No | - |
| updated_at | timestamptz | Yes | - |
| expires_at | timestamptz | Yes | - |
| cart_items | |||
| id | uuid | No | PK |
| cart_id | uuid | No | - |
| product_id | uuid | No | - |
| quantity | integer | No | - |
| unit_price_cents | integer | No | - |
| added_at | timestamptz | No | - |
| orders | |||
| id | uuid | No | PK |
| customer_id | uuid | No | - |
| status | varchar | No | - |
| total_cents | integer | No | - |
| currency | varchar(3) | No | - |
| shipping_address_id | uuid | Yes | - |
| billing_address_id | uuid | Yes | - |
| placed_at | timestamptz | No | - |
| paid_at | timestamptz | Yes | - |
| shipped_at | timestamptz | Yes | - |
| order_items | |||
| id | uuid | No | PK |
| order_id | uuid | No | - |
| product_id | uuid | No | - |
| quantity | integer | No | - |
| unit_price_cents | integer | No | - |
| total_cents | integer | No | - |
| created_at | timestamptz | No | - |
| payments | |||
| id | uuid | No | PK |
| order_id | uuid | No | - |
| provider | varchar | No | - |
| provider_payment_id | text | Yes | - |
| amount_cents | integer | No | - |
| currency | varchar(3) | No | - |
| status | varchar | No | - |
| created_at | timestamptz | No | - |
| reviews | |||
| id | uuid | No | PK |
| product_id | uuid | No | - |
| customer_id | uuid | No | - |
| rating | smallint | No | - |
| title | varchar | Yes | - |
| body | text | Yes | - |
| created_at | timestamptz | No | - |
Frequently asked questions
What tables does an e-commerce database need?
A standard e-commerce schema needs customers, addresses, categories, products, product_images, carts, cart_items, orders, order_items, payments, and reviews — 11 tables covering catalog, cart, checkout, and post-purchase flows.
What is the difference between cart_items and order_items?
cart_items are mutable — quantities change until checkout. order_items are immutable snapshots copied from the cart at order time, preserving the price and product that was actually purchased even if the product later changes.
Visualize your own database
Paste your PostgreSQL connection string and get an interactive ER diagram of your own schema in under 10 seconds. No signup required.
Try it free →