# E-commerce data model

> An example e-commerce data model with ER diagram — customers, addresses, product categories, products, carts, orders, order lines, payments and shipments — with a data dictionary and PostgreSQL DDL you can open and adapt.

Source: https://entitymodel.com/docs/examples/ecommerce/

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.

<!-- diagram -->

**[Open this model in EntityModel.com](https://entitymodel.com/#/share/v1.1Vt7b-O4Ef8qhPrHtaiTk_zIwwWKam0na1w2cWOnh7tiITASYxORSS1FZdc97Hcv-JBESbQt27lDCyywMV8zHM5vZjgc_ea8ULaG3Bk6iHDMN2saoRgxp6M7gjfEUkyJM_Q6TkozFiJn6Kw4T9Lhjz8ac85DunY6DoFrMQCFdL1GYnDH4ZjHom1yljeCOadMdFG2hAT_B3JJwZnI5T7lywH0Da6TGKVOx4lQGjKc6IE-AZTEmCCQipWGIMxSTteIpR0Ao4ihNEXiT5AwGmUhByHkMKZL8BXzlfiBlpRhMSRd0STBZAlCyHjaAZRFchX5PxAk0g5I4GaNCE8BJBFIVziRv86djlNIx_HOXafjwIyvKGtuxek4KYc8S52hA5OE0TcUOR0nZAhyFAXyALpu9-LM887c3sJ1h_Lfueu6vzodJ0uiVuPkeWCUOsN__5YfRS4aIUScJjHcBLpnVPaogwz4JhEdKYckgkxySGPKgpBGov1PvcmFf3XTPA6QIJZSosQLCYBhSDPCASWAr_QhCXFBzhl-zridwwALijF8RrHBHZiOhRIpzj5Mb6f3C6fj4DRIGF5Dtgle0cYZcpahjsPQlwwzFOW_q2zOM8boEnIEXtFGsKP1S5ye63b7A-d7p2AKrSGODXYm-rdmZL54nN7fOk2SGcFfMpT_SjC283JHl5hIfQop4TDkQBKscgUJ_If-JZXIYO8li2N1jiWLN1kcg3vVVmcTp4EeZpeUwWhJ3ycQzHH8Bk3KyYoSk-pM_65TtK74Vw8MBgPgeq5rrmngwFAA1Qh8Xq6-mH6azBf-p5lF8lUB_7xCSvlyXfwKU6DpVKUs8eT2zrz-wnOH3e5QYMlgbg3ZK-KYLAOa8AATg8VPeRd4SDiYEkNRHx7uJv79XjZHlKSIcMApKOjYVEHMdb5_FmaEZSGXSBUg-txxIEPQwEtq8q6NYRP7ftHREvqX_mB0NbFAX9hDaUEpA884jsWfmixI4RuKwPMGwMJCbzcDelLVCmg-TzECpRAHg55XUbsjDM_u41ysULFT8HVFtQSkHqqd7LM7uRSE67HI4Q5bwXYAvGs2kTOEuDREJFs_q_Mx-OuCj5A904yBOa-IDvONKTP1c59xLBeeUcZjSKKKZaEph7HSOMO-yFYwUq37KFitzvVl160evLAIbFMnNVLNNVqPk5vJ4-R-NAkm94vp4hdJ9QUxREQs9JsGULmqiGsynmQ8KPS8TvL7LtE8zU1WcRpE6AVmsWkapykYF40HWhxTRX9IgV68hLFVT480PzrS2lhij7KnpQHybi56FxcWA0RohABWxt6I9-TyQKh3B6RZuAIwBfMVRSn4O3jMCMFkuSMk0fNrliFf1bQMx5kir2uTU92jFwSP9OolPb3jCtwgQ4QH9q3OZCfYseOmVqkFC9n_DaB1wjfghTLAaXIWozcUG8F3VcN6e9RLBfAV_tVZN3VrVnS0VK1rv-97ru2WgTlayw2kMEbnYMZwiFIAGRIRL8gI5rnmyRgXhBkTdmGzXbM01zVpa7V9Fy93deVWtSt9zQxa85-eDg5j6z6Dhq_gFSFpL4QQqkf5-HR_tni8O-t3zz7c_WQ5srqa59s_WcsXDOJYohsx0K8IwdxCSXlcaa0TrofsZAmMJnF3_EryO05ueRK4ROdWbLc1JW2NhhC80IzQFOWTUMmZbtTLjiej6Sf_TsTkDIVYX-e7HScNoVi3uzdIlVrNQYxTDiTFepRwfV4L6DUOGh5Wtx_tYvX8LT62QnWPkx1XICJUOviSQUXIQIvU9X-WHTuPqio2cRYpgG8Qx_A5RiLGT1FcC-x7lzVvD0OO31DV2ft524G-fi41FNdu4Qe59abdFZkSm0NnB1jcvt8fD7rW24SRj-mAZ_QizeoKha8047scNuN1hLF3MqiXPdftnnxxqO50UrjGZYZSrtJP--4GR12T21tPlVhyz9zuwrsa9l11Ed6lHQ8yW1ZXjkB4TbuGgKnqaqkm3ZvB-LJnyzdpayvuLTlqhZ5DKch9asLR2qYrwte_i8JcX3tdt9cUSzv1PEQRD48mDogbLOZwmyFsr2TewRqVY02mZJtq9aCbW6rUYHR5c23LYyQxDFGkEr_nYEE5jFWkJ-1mJK4P4QqyJYo6IKUghhwx5QxFO1miFEQUEMoBjEXXCouJOyJBSal6cnIv75TrcF3X61aUUBFU1_wG0fu8-dDoa1ekqBbVgRKn0g2ZiaCS3fnDmcrDNbh-jyTNLqOaX3wDa_5pnl-Ldyai2qec1Bmot4B6cKQOYi77jg2QzPXtQZJJe2eINPOn4wbrde_zoBpbJ2m3eZtBd-h5v_5fh5DZMxdGI4BrkeoxdUj3AD_vea_AfJ6tAX2RsDIerPYF56XGNzgttP10Tg23dT6o0OfwW5P0An57X6qee96ruLMtp7P4fY5GLZi7jNqR9C_Or7qHOkIFbZ0XtjnBPDnc0hP6XvfGu2p4wgdSXmapeKJSqtVRj3vqqqudXr6zXf5N8GtzcoLXdwrPL123P7BY2bau9SAn-r8Sce2MsH6fJMG-SKCqR3J9ALlUGY7XqGKp7DbqMEDoZ3lLArDoOPldW60EIOcimQngEmKS8gIV52AEWQQixCHW4eIKxfLJSyJFTxcP_liCSMSHK7TrNVzPqadj1ULvApgLTwDmj8KL3s4a8RWNGo87el-fZO-xLr1Kw-7YTfo73frIfxzXAC_PLihpV4AvO8Gj0bkvgrZlzqt68kMKOIMkFbkgSsB0XIVLgoPe7Ev3_LySaGx4tvd1p9pnmSeLa8HgDOLtkaAt_5ERjuMKUtIsDBGKUssTfSVWHBycmcjrdprmYl72nPoYnkAWohjIJ32R3CntxJ3wlrq8CISQyAc3kbJI0RtiMNZT0-2GId9A84Lyfqah13Vd7_IPMg0hZAxXrqKjoqX9S_LTrPJeyhkMX0Vw27jnLnRP-6uuof2_Xl9f-74nttAfXFxe9RsBdf1mNFeNW_Fg0e7ewr0eem69ACVCMX5rXr3GefMhJAYLrz_secek9mpXS2sEOs97q0AyTWctETy5HF2NrM-JKYciqZJDCKzpG0oBXzGaLVc7YGK9W1tv1ebBHwST-tVY06w9a2maJ79qCbO65bAKwQZvMM60IEwJDJ3Z5H6sKJpcDp0ZIpFAQx5Fie3UZvoS47VpkpnG2PnH6Ww2aQ7XKLDMGE_upv-aPFrmFIptmTXy70eTuzvLrBEkIYpj66zHyc3T_dgy6RG9ZCRCSrx1YQZFvetEnQXQQt4a3mwNRXWAczowfoab1Kimkp4kgTuyjPa4yxpvHQ2HesikadbgoGmeDIcRQxHmotwsQs9YPp0cBA9TIpr3ToXlbSQ69bkz_5eZjKeqs2dwM4OxZbw_m91Ngpn_S2OKnwj1msGNZdbt9GYRWNm8xS_G9o9R37Jkqf5YU3ScqLBqIVlrjSOx1gtG-f0IMzCdP4Ced3Fx5gEYJyt41gViwR0vOIeUbh2o0VXOd7C2rVgrZ65exqOZO1L1a5EzWcY4XYmkOuNAUKpxRLB4DhTOR5x1e1xUJCt31qluqLF2pzFp5DcnjSCBEbSNvv2wlcRPmCwjXfJcmzWeNGfdIraGZGMbfvPYHH7DhLOwjfafmqP9LOUMxhjugZmhMH9G30LEEv4XW0bbVolf9JwKN7XSTrz1u97lPpQdlnw_GWcVlram2Auu6vjKuTrZtzzNwZjGMWQHAaciLMV1p8ZsZelOc87It82R0MGQ7Jp5-2FmmTmjGRFARSzOy_7qEydPj5aJk4xR63D_ycZhgQ1iCm43RuRhGwiRE2L5EZC4SVWjeVE_HTwLZBRrlEXtXFyqefUjF-EPMYGxSg3dDz31LdNXyKKgiMxlVfaHjbxYim94UKVLLP2MI8yQzL_ALZkbvxqGCVaL23zxFVIDY58lOwgviYJI5YGz-p5UlI7RF3P_RlVtKYCyrZUAysXBw4tFDB9FtWw5qJ1ARkVtp0h-rAGU5bf7JWCpRDUEIarOUKQ-vChkUBZ_HiuCO7ms-mijvnvR11IJJjBcFe8lK5gCKt9PpA0sSmFbKIF9788opmSZBpwGhoobmsD4CTD4oFYHC7pFA3RRVwsx3JZVTGAlgEDo9k8-2kJAFeqQ-o7z-iLj5CWbbbasinxsZz4S32Fh0vLYWxyoqvipb6dU3N17Kse12lYKbiiz7GpKgFl39R47M17AzIuwLJ-pWepG7c6xmjpTxTlWiy372qL1DbGNzidpbIm6FIHZk7VV-q6A03YCKJ1YK3st1rbj9BGFCLf2WXs3ZyuHqRhjgmruqPIu3TjgYuftbDJB2_2Rft5u45nznGFukKlIHzJU1kfsFkKR9K7v3IJe-_YPg69Otja3nZfXPJDfF7xwkwYvtOJbytfV0850JjJWduMk34s-tAwubiCORRmeeolVb62vKOGyGq9IEotAbGUGYvm328cduopHDakYr0iniWWuV27En_rZYPpeJ15u63PHWWHEIAtX2Mz26z8J1St8_y8)** to explore it, change it and export it. Nothing is uploaded: the model travels in the link. Or [download model.json](/docs/examples/ecommerce/model.json).

## Entities

| Entity | Type | Description | Primary Key | Display Attribute | Attributes |
| --- | --- | --- | --- | --- | --- |
| Address | Entity | A shipping or billing address saved by a customer. | address_id | address_line | 7 |
| Cart | Entity | A shopping cart, before checkout. | cart_id | created_at | 3 |
| Cart Item | Entity | A product and quantity in a cart. | cart_item_id | quantity | 4 |
| Category | Entity | A node in the product category tree, such as Shoes > Running. | category_id | category_name | 3 |
| Customer | Entity | A person with an account on the store. | customer_id | full_name | 6 |
| Order | Entity | A placed order. Totals are stored as charged, so later price changes do not alter history. | order_id | order_number | 11 |
| Order Line | Entity | One product on an order, with the price charged. | order_line_id | unit_price | 5 |
| Payment | Entity | A payment attempt against an order. Card details are held by the payment provider, not here. | payment_id | provider_reference | 6 |
| Product | Entity | An item for sale. Prices are per unit in the store currency. | product_id | product_name | 9 |
| Shipment | Entity | A parcel sent for an order. Large orders can ship in several parcels. | shipment_id | tracking_number | 6 |
| Country | Reference | Countries, identified by their ISO 3166-1 alpha-2 code. | country_code | country_name | 2 |
| Currency | Reference | Currencies, identified by their ISO 4217 code. | currency_code | currency_name | 2 |
| Order Status | Reference | The stages an order moves through. | status_code | status_name | 2 |
| Payment Method | Reference | Ways a customer can pay. | method_code | method_name | 2 |

## Attributes

### Address

A shipping or billing address saved by a customer.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| address_id | BIGINT | PK, Required |  | 5531 |  |
| customer_id | BIGINT | Required |  | 100245 | The customer who saved the address. |
| address_line | STRING | Label, Required, PII |  | 12 Harbour St | Street and number. |
| city | STRING | Required |  | Portland |  |
| postal_code | STRING | Required, PII |  | 97201 |  |
| country_code | REFERENCE | Required | → country.country_code | US |  |
| is_default | BOOLEAN | Required |  | true | The customer's default shipping address. |

### Cart

A shopping cart, before checkout.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| cart_id | BIGINT | PK, Required |  | 730021 |  |
| customer_id | BIGINT |  |  | 100245 | Empty for guest carts. |
| created_at | TIMESTAMP | Label, Required |  | 2026-10-02T18:40:00Z |  |

### Cart Item

A product and quantity in a cart.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| cart_item_id | BIGINT | PK, Required |  | 991203 |  |
| cart_id | BIGINT | Required |  | 730021 |  |
| product_id | BIGINT | Required |  | 88012 |  |
| quantity | INT | Label, Required |  | 1 |  |

### Category

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| category_id | INT | PK, Required |  | 12 |  |
| category_name | STRING | Label, Required |  | Running |  |
| parent_category_id | INT |  |  | 3 | The parent category; empty for top-level categories. |

### Customer

A person with an account on the store.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| customer_id | BIGINT | PK, Required |  | 100245 | Surrogate key. |
| email | STRING | Required, PII |  | ana@example.com | Login and contact email. |
| full_name | STRING | Label, Required, PII |  | Ana Silva |  |
| phone | STRING | PII |  | +1 555 0100 |  |
| created_at | TIMESTAMP | Required |  | 2026-03-14T10:22:00Z | When the account was created. |
| marketing_opt_in | BOOLEAN | Required |  | true | Consent to marketing email. |

### Order

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| order_id | BIGINT | PK, Required |  | 5000123 |  |
| order_number | STRING | Label, Required |  | SO-2026-000123 | Number shown to the customer. |
| customer_id | BIGINT | Required |  | 100245 |  |
| shipping_address_id | BIGINT | Required |  | 5531 |  |
| order_status_code | REFERENCE | Required | → order_status.status_code | PAID |  |
| ordered_at | TIMESTAMP | Required |  | 2026-10-02T18:52:11Z |  |
| currency_code | REFERENCE | Required | → currency.currency_code | USD |  |
| subtotal_amount | DECIMAL(12,2) | Required |  | 129.00 | Sum of the order lines. |
| shipping_amount | DECIMAL(12,2) | Required |  | 7.50 |  |
| tax_amount | DECIMAL(12,2) | Required |  | 10.32 |  |
| total_amount | DECIMAL(12,2) | Required |  | 146.82 | Amount charged. |

### Order Line

One product on an order, with the price charged.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| order_line_id | BIGINT | PK, Required |  | 7700451 |  |
| order_id | BIGINT | Required |  | 5000123 |  |
| product_id | BIGINT | Required |  | 88012 |  |
| quantity | INT | Required |  | 1 |  |
| unit_price | DECIMAL(12,2) | Label, Required |  | 129.00 | Price at the time of the order. |

### Payment

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| payment_id | BIGINT | PK, Required |  | 610045 |  |
| order_id | BIGINT | Required |  | 5000123 |  |
| payment_method_code | REFERENCE | Required | → payment_method.method_code | CARD |  |
| provider_reference | STRING | Label |  | pi_3Pq2... | The payment provider's transaction ID. |
| amount | DECIMAL(12,2) | Required |  | 146.82 |  |
| paid_at | TIMESTAMP |  |  | 2026-10-02T18:52:15Z | Empty until the payment succeeds. |

### Product

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| product_id | BIGINT | PK, Required |  | 88012 |  |
| sku | STRING | Required |  | RUN-TRL-42-BLK | Stock keeping unit. |
| product_name | STRING | Label, Required |  | Trail Runner 42 |  |
| description | STRING |  |  |  | Long description shown on the product page. |
| category_id | INT | Required |  | 12 |  |
| unit_price | DECIMAL(12,2) | Required |  | 129.00 | Current list price. |
| currency_code | REFERENCE | Required | → currency.currency_code | USD |  |
| stock_quantity | INT | Required |  | 37 | Units available to sell. |
| is_active | BOOLEAN | Required |  | true | Shown in the store. |

### Shipment

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| shipment_id | BIGINT | PK, Required |  | 320017 |  |
| order_id | BIGINT | Required |  | 5000123 |  |
| carrier | STRING | Required |  | UPS |  |
| tracking_number | STRING | Label |  | 1Z999AA10123456784 |  |
| shipped_at | TIMESTAMP |  |  | 2026-10-03T09:10:00Z |  |
| delivered_at | TIMESTAMP |  |  | 2026-10-05T14:31:00Z |  |

### Country

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

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| country_code | STRING | PK, Required |  | US | ISO 3166-1 alpha-2 code. |
| country_name | STRING | Label, Required |  | United States | English short name. |

### Currency

Currencies, identified by their ISO 4217 code.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| currency_code | STRING | PK, Required |  | USD | ISO 4217 code. |
| currency_name | STRING | Label, Required |  | US Dollar |  |

### Order Status

The stages an order moves through.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| status_code | STRING | PK, Required |  | PAID |  |
| status_name | STRING | Label, Required |  | Paid |  |

### Payment Method

Ways a customer can pay.

| Attribute | Data Type | Flags | Reference | Example | Description |
| --- | --- | --- | --- | --- | --- |
| method_code | STRING | PK, Required |  | CARD |  |
| method_name | STRING | Label, Required |  | Credit or debit card |  |

## Relationships

| Relationship | Source | Target | Cardinality | Forward Label | Reverse Label | Key | Description |
| --- | --- | --- | --- | --- | --- | --- | --- |
| saved_by | Address | Customer | N:1 | Saved By | Saves | address.customer_id | A customer can save several addresses. |
| subcategory_of | Category | Category | N:1 | Subcategory Of | Has Subcategory | category.parent_category_id | Categories form a tree. |
| listed_in | Product | Category | N:1 | Listed In | Lists | product.category_id | Each product has one primary category. |
| belongs_to_customer | Cart | Customer | N:1 | Belongs To | Has Cart | cart.customer_id | Guest carts have no customer. |
| item_in | Cart Item | Cart | N:1 | Item In | Contains | cart_item.cart_id |  |
| item_product | Cart Item | Product | N:1 | Is For | In Cart Item | cart_item.product_id |  |
| placed_by | Order | Customer | N:1 | Placed By | Places | customer_order.customer_id | Every order belongs to one customer. |
| ships_to | Order | Address | N:1 | Ships To | Receives | customer_order.shipping_address_id |  |
| line_of | Order Line | Order | N:1 | Line Of | Has Line | order_line.order_id | An order has one or more lines. |
| line_product | Order Line | Product | N:1 | Orders | Ordered On | order_line.product_id |  |
| pays_for | Payment | Order | N:1 | Pays For | Paid By | payment.order_id | Failed attempts are kept, so an order can have several payments. |
| ships | Shipment | Order | N:1 | Ships | Shipped In | shipment.order_id |  |

## Reference data

### Country

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

| country_code | country_name |
| --- | --- |
| US | United States |
| CA | Canada |
| GB | United Kingdom |
| DE | Germany |
| FR | France |
| AU | Australia |

### Currency

5 values · Source: ISO 4217 (excerpt)

| currency_code | currency_name |
| --- | --- |
| USD | US Dollar |
| CAD | Canadian Dollar |
| GBP | Pound Sterling |
| EUR | Euro |
| AUD | Australian Dollar |

### Order Status

6 values · Source: Example values

| status_code | status_name |
| --- | --- |
| PENDING | Pending payment |
| PAID | Paid |
| SHIPPED | Shipped |
| DELIVERED | Delivered |
| CANCELLED | Cancelled |
| REFUNDED | Refunded |

### Payment Method

4 values · Source: Example values

| method_code | method_name |
| --- | --- |
| CARD | Credit or debit card |
| PAYPAL | PayPal |
| APPLE_PAY | Apple Pay |
| GIFT_CARD | Gift card |

## 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.

```sql
-- 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.

```mermaid
---
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.