E-commerce database (ER)
Customers, orders, products, and payments.
Every online store ends up with roughly this schema, but teams usually
reconstruct it from memory during planning meetings. This entity-relationship
diagram writes it down once: customers place orders, each order contains line
items that reference products, and every order is settled by exactly one
payment. The ORDER_ITEM junction table — the piece most often hand-waved on
whiteboards — is modeled explicitly, with the foreign keys and quantity column
that make a many-to-many relationship work.
Each entity carries a realistic attribute block (UUIDs as primary keys, money stored as integer cents, a status column on orders), so the diagram reads like a schema review rather than a sketch.
When to use this template
- Schema design sessions — put a concrete starting point on screen instead of drawing boxes from scratch, then argue about what's different in your domain.
- Onboarding backend engineers — five entities and four relationships explain the heart of the data model faster than reading migration files.
- API and integration docs — partners integrating with your orders or payments endpoints can see how resources relate without database access.
How to adapt it
Rename entities to match your tables, then extend where your domain diverges. Typical additions:
- Add an ADDRESS entity linked to both customer (saved addresses) and order (shipping snapshot) — a classic source of modeling debates worth settling in the diagram.
- Split PAYMENT into payment attempts with a status column if you support retries, partial refunds, or multiple providers.
- Add INVENTORY or STOCK_LEVEL referencing product, so warehouse logic has a home in the model.
If you'd rather not hand-write crow's-foot syntax, edit the diagram visually — the editor regenerates clean Mermaid code from your changes, ready to paste into a README or design doc.
Mermaid code
Copy it anywhere Mermaid is supported — GitHub, Notion, or your docs.
erDiagram
CUSTOMER ||--o{ ORDER : places
ORDER ||--|{ ORDER_ITEM : contains
PRODUCT ||--o{ ORDER_ITEM : "appears in"
ORDER ||--|| PAYMENT : "paid by"
CUSTOMER {
uuid id PK
string email
string name
}
ORDER {
uuid id PK
uuid customer_id FK
string status
timestamp created_at
}
ORDER_ITEM {
uuid id PK
uuid order_id FK
uuid product_id FK
int quantity
}
PRODUCT {
uuid id PK
string name
int price_cents
}
PAYMENT {
uuid id PK
uuid order_id FK
string provider
int amount_cents
}
Frequently asked questions
- How do I draw an ER diagram in Mermaid?
- Start a code block with `erDiagram`, then declare relationships one per line using crow's-foot notation — for example `CUSTOMER ||--o{ ORDER : places`. Entities get attribute blocks in curly braces listing a type, a name, and optional PK/FK markers. Mermaid lays the diagram out automatically, so you only describe the schema, never pixel positions.
- What do symbols like ||--o{ mean in a Mermaid ER diagram?
- Each end of the relationship encodes cardinality: `||` means exactly one, `o|` zero or one, `}o` zero or more, and `}|` one or more. So `CUSTOMER ||--o{ ORDER` reads as "one customer places zero or more orders," while `ORDER ||--|| PAYMENT` is a strict one-to-one. The text after the colon is just the relationship label.
- What tables does a basic e-commerce schema need?
- Five cover the core: customers, orders, products, payments, and an order_items junction table. The junction table is the key design decision — it resolves the many-to-many between orders and products and is where per-line data like quantity and the price actually charged belongs. Everything else (inventory, addresses, discounts) hangs off these.
- Can I turn this ER diagram into actual SQL tables?
- The diagram maps almost one-to-one onto DDL: each entity block becomes a CREATE TABLE, the PK/FK markers become constraints, and the crow's-foot cardinalities tell you where foreign keys and NOT NULL belong. Treat it as the design document you review before writing migrations, not as generated code — Mermaid doesn't enforce types.