Online Shop ER Diagram (Crow's Foot)

This sample models the core of an online shop: customers place orders, orders contain line items, each line points at a product, and products are grouped into a category tree. It uses crow's foot notation, the most common way to draw an entity-relationship (ER) diagram.

The line ends tell you how many rows on one side can relate to rows on the other, which is exactly what foreign keys and NOT NULL constraints will later enforce.

1. The Diagram

Online Shop: Entity-Relationship Diagram (crow's foot) customer PK customer_id BIGINT UK email VARCHAR full_name VARCHAR phone VARCHAR created_at TIMESTAMP orders PK order_id BIGINT FK customer_id BIGINT order_date DATE status VARCHAR total_amount DECIMAL order_item PK,FK order_id BIGINT PK line_no INT FK product_id BIGINT quantity INT unit_price DECIMAL product PK product_id BIGINT FK category_id BIGINT UK sku VARCHAR name VARCHAR list_price DECIMAL category PK category_id BIGINT FK parent_id BIGINT name VARCHAR places contains sold as classifies parent of (self-reference) Business rules captured by the line ends A customer may place zero or many orders; every order has exactly one customer. An order holds one or many order lines; a line belongs to exactly one order. A product appears on zero or many lines; each line points to exactly one product. Each product sits in exactly one category; a category may be empty. A category has zero or one parent, giving a tree (Books > Fiction > Crime). order_item keeps its own unit_price so later price changes do not alter history.

The diagram has five entities and five relationships. Read each line twice, once from each end.

  1. Start at customer. The line to orders has two bars on the customer side (exactly one) and a circle plus crow's foot on the orders side (zero or many): a new customer can exist with no orders, but no order exists without a customer.
  2. orders to order_item is one-to-one-or-many. An order with zero lines is not allowed, which the application enforces at checkout.
  3. order_item has a composite primary key (order_id, line_no). order_id is also a foreign key, so the line cannot outlive its order.
  4. product to order_item: a product may never have been sold (zero or many lines), but each line names exactly one product.
  5. category classifies products, and its parent_id loop makes a category tree. The circle on the parent end means top-level categories have no parent.
  6. Unique keys (UK) mark natural identifiers such as email and sku that must not repeat even though the surrogate key is the PK.

2. How to Read It

Crow's Foot Line Endings E exactly one (mandatory one) Mermaid: || E zero or one (optional one) Mermaid: o| E one or many (mandatory many) Mermaid: |{ E zero or many (optional many) Mermaid: o{

Each relationship line ends in two symbols. The symbol closest to the entity is the maximum (bar = one, crow's foot = many); the outer symbol is the minimum (circle = zero/optional, bar = one/mandatory). Inside each box, PK marks the primary key, FK a foreign key and UK a unique key. Verbs on the lines read left to right or top to bottom.

3. Variant: Resolving Many-to-Many

Resolving Many-to-Many: orders and products orders PK order_id product PK product_id conceptual M:N cannot be stored directly orders PK order_id order_item PK,FK order_id PK line_no FK product_id product PK product_id junction table: two 1:N lines replace one M:N

Conceptually an order contains many products and a product is in many orders. Relational databases cannot store a many-to-many line, so the logical model adds order_item as a junction (associative) entity. It also becomes a natural home for facts about the pairing, such as quantity and the price actually charged.

4. Pros, Cons, and When to Use

Pros

Cons

When to use

When not to use

5. As Diagrams-as-Code

Mermaid erDiagram uses the same crow's foot symbols in text form:

erDiagram
    customer   ||--o{ orders     : places
    orders     ||--|{ order_item : contains
    product    ||--o{ order_item : "sold as"
    category   ||--o{ product    : classifies
    category   |o--o{ category   : "parent of"
    customer {
        bigint customer_id PK
        varchar email UK
        varchar full_name
    }
    orders {
        bigint order_id PK
        bigint customer_id FK
        date order_date
        varchar status
    }
    order_item {
        bigint order_id PK, FK
        int line_no PK
        bigint product_id FK
        int quantity
        decimal unit_price
    }

dbdiagram.io (DBML) and PlantUML's entity syntax express the same model.