E-commerce Database ER Diagram with Orders and Payments

Mermaid code

erDiagram
    CUSTOMER ||--o{ ADDRESS : has
    CUSTOMER ||--o{ ORDER : places
    ORDER ||--|{ ORDER_ITEM : contains
    ORDER ||--o| PAYMENT : "paid by"
    PRODUCT ||--o{ ORDER_ITEM : "appears in"
    CATEGORY ||--o{ PRODUCT : groups
    CUSTOMER {
        int id PK
        string email UK
    }
    ORDER {
        int id PK
        int customer_id FK
        string status
        decimal total
    }
    ORDER_ITEM {
        int id PK
        int order_id FK
        int product_id FK
        int quantity
        decimal unit_price
    }

Paste this into any tool that renders Mermaid, or into the editor to change the theme and export PNG or SVG. Syntax reference: Entity Relationship guide.

What this diagram shows

The relationship lines at the top carry the rules. CUSTOMER ||--o{ ORDER reads as one customer places zero or more orders. ORDER ||--|{ ORDER_ITEM requires at least one item per order. ORDER ||--o| PAYMENT allows an unpaid order with at most one payment. ORDER_ITEM stores unit_price so a later price change on PRODUCT does not rewrite history. ADDRESS, CATEGORY, PRODUCT, and PAYMENT are declared through relationships only, which keeps the diagram focused on the order path; add attribute blocks for them as your schema settles.

How to adapt it

• Change ORDER ||--o| PAYMENT to ORDER ||--o{ PAYMENT if you support split or retried payments.
• Add a PRODUCT_CATEGORY join entity and make CATEGORY }o--o{ PRODUCT if products belong to several categories.
• Add an ADDRESS attribute block with int customer_id FK and string type to distinguish billing from shipping addresses.
• Rename string status to an explicit list in a comment, for example %% status: pending, paid, shipped, cancelled, so the allowed values travel with the diagram.
• Add INVENTORY with int product_id FK and int on_hand if stock is tracked per warehouse.

FAQ