Erd

Entity-Relationship Diagrams (ERD)

Overview

Entity-Relationship Diagrams model the structure of data by defining entities, their attributes, and the relationships between them. ERDs are the foundational tool for database design, used from early conceptual modeling through to physical schema implementation.

ERDs operate at three levels of abstraction:

• Conceptual ERD — High-level entities and relationships, no attributes. Used for business communication.
• Logical ERD — Entities, attributes, primary keys, and relationships with cardinality. Database-agnostic.
• Physical ERD — Table names, column types, indexes, constraints. Maps directly to a specific DBMS.

Entity Types

An entity represents a "thing" or concept about which data is stored.

Attributes

Attributes describe properties of an entity.

Relationships

Relationships connect entities and describe how they are associated.

Relationship Types

Participation (Optionality)

Cardinality and Participation Combined

Cardinality:     Minimum .. Maximum

Examples:
  0..1    Zero or one (optional, at most one)
  1..1    Exactly one (mandatory, at most one)
  0..*    Zero or many (optional, unbounded)
  1..*    One or many (mandatory, at least one)

Chen Notation

Chen notation (original ER notation by Peter Chen, 1976) uses geometric shapes:

┌───────────┐          ┌──────────────┐         ┌───────────┐
│           │          │              │         │           │
│ CUSTOMER  │══════════◇   places     ◇════════│  ORDER    │
│           │  1       │              │   N     │           │
└─────┬─────┘          └──────────────┘         └─────┬─────┘
      │                                               │
   ┌──┴──┐                                        ┌──┴──┐
   │Name │                                        │Date │
   └─────┘                                        └─────┘
  (attribute)                                    (attribute)

   ┌─────────┐
   │_CustID_ │   ← underlined = Primary Key
   └─────────┘

Shapes:
  Rectangle     = Entity
  Diamond       = Relationship
  Oval          = Attribute
  Double rect   = Weak Entity
  Double diamond= Identifying Relationship (for weak entities)
  Double oval   = Multi-valued Attribute
  Dashed oval   = Derived Attribute

Chen Notation Cardinality

• Numbers (1, N, M) are placed on the lines connecting entities to the relationship diamond
• Double lines indicate total (mandatory) participation
• Single lines indicate partial (optional) participation

Crow's Foot Notation

Crow's Foot notation (also called IE notation or Martin notation) is the most widely used notation in database tools and is more compact than Chen notation.

Crow's Foot Symbols

Cardinality Symbols (at the end of relationship lines):

    ──────||    Exactly one (mandatory one)
    ──────|O    Zero or one (optional one)
    ──────|<    One or many (mandatory many)
    ──────O<    Zero or many (optional many)

Where:
    ||  = "one and only one" (two bars)
    |O  = "zero or one" (bar + circle)
    |<  = "one or many" (bar + crow's foot)
    O<  = "zero or many" (circle + crow's foot)

Crow's Foot Reading Convention

Read relationships from each entity's perspective:

┌──────────┐                      ┌──────────┐
│ CUSTOMER │ ──────||────────O< ──│  ORDER   │
└──────────┘                      └──────────┘

Reading left-to-right:
  "A CUSTOMER places zero or many ORDERs"

Reading right-to-left:
  "An ORDER is placed by exactly one CUSTOMER"

Crow's Foot Full Example

┌──────────────┐         ┌──────────────┐         ┌──────────────┐
│  CUSTOMER    │         │   ORDER      │         │  PRODUCT     │
├──────────────┤         ├──────────────┤         ├──────────────┤
│ PK CustomerID│──||──O<─│ PK OrderID   │         │ PK ProductID │
│    Name      │         │ FK CustomerID│   ┌─O<──│    Name      │
│    Email     │         │    OrderDate │   │     │    Price     │
│    Phone     │         │    Total     │   │     │    Category  │
└──────────────┘         └──────┬───────┘   │     └──────────────┘
                                │           │
                         ┌──────┴───────┐   │
                         │ ORDER_ITEM   │   │
                         ├──────────────┤   │
                         │ PK,FK OrderID│──||┘
                         │ PK,FK ProdID │
                         │    Quantity  │
                         │    UnitPrice │
                         └──────────────┘

Resolves the M:N between ORDER and PRODUCT
via the associative entity ORDER_ITEM.

Chen vs Crow's Foot Comparison

Mermaid ERD Syntax

Mermaid provides native support for ER diagrams using Crow's Foot-style notation.

Relationship Symbols

Relationships are written as: EntityA <relationship> EntityB : "label"

Attribute Types

Mermaid ERD attributes support type, name, and constraint markers:

erDiagram
    ENTITY {
        type attribute_name PK "comment"
        type attribute_name FK
        type attribute_name UK "unique key"
    }

Complete Mermaid ERD Example: E-Commerce Database

erDiagram
    CUSTOMER {
        int customer_id PK
        string first_name
        string last_name
        string email UK
        string phone
        date created_at
    }

    ADDRESS {
        int address_id PK
        int customer_id FK
        string street
        string city
        string state
        string zip_code
        string country
        boolean is_primary
    }

    ORDER {
        int order_id PK
        int customer_id FK
        int shipping_address_id FK
        datetime order_date
        string status
        decimal total_amount
    }

    ORDER_ITEM {
        int order_item_id PK
        int order_id FK
        int product_id FK
        int quantity
        decimal unit_price
        decimal line_total
    }

    PRODUCT {
        int product_id PK
        int category_id FK
        string name
        string description
        decimal price
        int stock_quantity
        string sku UK
    }

    CATEGORY {
        int category_id PK
        int parent_category_id FK
        string name
        string description
    }

    PAYMENT {
        int payment_id PK
        int order_id FK
        string payment_method
        decimal amount
        string status
        datetime payment_date
        string transaction_ref UK
    }

    REVIEW {
        int review_id PK
        int product_id FK
        int customer_id FK
        int rating
        string comment
        datetime review_date
    }

    CUSTOMER ||--o{ ADDRESS : "has"
    CUSTOMER ||--o{ ORDER : "places"
    CUSTOMER ||--o{ REVIEW : "writes"
    ORDER ||--|{ ORDER_ITEM : "contains"
    ORDER ||--o{ PAYMENT : "paid via"
    ORDER }o--|| ADDRESS : "shipped to"
    ORDER_ITEM }o--|| PRODUCT : "references"
    PRODUCT ||--o{ REVIEW : "receives"
    PRODUCT }o--|| CATEGORY : "belongs to"
    CATEGORY ||--o{ CATEGORY : "parent of"

Mermaid ERD Relationship Syntax Reference

erDiagram
    %% One-to-one (both mandatory)
    PERSON ||--|| PASSPORT : "holds"

    %% One-to-many (mandatory on both sides)
    DEPARTMENT ||--|{ EMPLOYEE : "employs"

    %% One-to-many (optional on many side)
    CUSTOMER ||--o{ ORDER : "places"

    %% Many-to-many (resolved via junction table)
    STUDENT }o--o{ COURSE : "enrolls in"

    %% One-to-zero-or-one
    EMPLOYEE ||--o| PARKING_SPOT : "assigned"

Normalization

Normalization is the process of organizing data to reduce redundancy and improve integrity. Each normal form builds on the previous one.

First Normal Form (1NF)

Rule: Every column contains only atomic (indivisible) values. No repeating groups or arrays.

Violation:

| OrderID | Products              |
|---------|----------------------|
| 1       | Widget, Gadget, Bolt |

1NF Corrected:

| OrderID | Product |
|---------|---------|
| 1       | Widget  |
| 1       | Gadget  |
| 1       | Bolt    |

Second Normal Form (2NF)

Rule: Must be in 1NF, and every non-key attribute must depend on the entire primary key (no partial dependencies).

Violation (composite key: OrderID + ProductID):

| OrderID | ProductID | ProductName | Quantity |
|---------|-----------|-------------|----------|
| 1       | 101       | Widget      | 5        |

ProductName depends only on ProductID, not on the full key.

2NF Corrected:

ORDER_ITEM:  | OrderID | ProductID | Quantity |
PRODUCT:     | ProductID | ProductName |

Third Normal Form (3NF)

Rule: Must be in 2NF, and no non-key attribute depends on another non-key attribute (no transitive dependencies).

Violation:

| EmployeeID | DepartmentID | DepartmentName |
|------------|-------------|----------------|
| 1          | 10          | Engineering    |

DepartmentName depends on DepartmentID, not on EmployeeID.

3NF Corrected:

EMPLOYEE:    | EmployeeID | DepartmentID |
DEPARTMENT:  | DepartmentID | DepartmentName |

Summary of Normal Forms

When to Denormalize

Denormalization intentionally reintroduces redundancy for performance or simplicity:

Denormalization trade-offs:

• Faster reads, slower writes
• Increased storage, reduced query complexity
• Risk of data inconsistency without proper update discipline

Design Process

A recommended workflow for database design using ERDs:

1. Identify Entities
   │  What are the core "things" the system manages?
   ▼
2. Define Attributes
   │  What properties describe each entity? Identify PKs.
   ▼
3. Identify Relationships
   │  How are entities related? Determine cardinality.
   ▼
4. Draw Conceptual ERD
   │  Entities and relationships only, no attributes.
   ▼
5. Refine to Logical ERD
   │  Add attributes, PKs, FKs, resolve M:N via junction tables.
   ▼
6. Normalize (1NF → 2NF → 3NF)
   │  Eliminate redundancy and anomalies.
   ▼
7. Denormalize Selectively
   │  Add calculated columns or merge tables for performance.
   ▼
8. Create Physical ERD
   │  Specify data types, indexes, constraints for target DBMS.

Best Practices

• Start with a conceptual ERD. Identify entities and relationships before worrying about attributes, types, or keys. Get agreement on the conceptual model first.
• Normalize to 3NF by default. Denormalize only when you have a measured performance problem. Premature denormalization causes data integrity issues.
• Use Crow's Foot notation for implementation. It is the most widely supported notation in database tools and maps directly to physical schemas.
• Use Chen notation for teaching and conceptual design. Its explicit shapes make it easier to explain ER concepts to non-technical stakeholders.
• Resolve many-to-many relationships. Every M:N relationship must be resolved into two 1:N relationships via an associative (junction) entity before physical implementation.
• Name entities as singular nouns. Use CUSTOMER not CUSTOMERS. The entity represents the concept, not a collection.
• Name relationships with verbs. "places", "contains", "belongs to" — not "has" (which is vague) or "link" (which is meaningless).
• Mark all keys explicitly. Every entity must have a PK. Every relationship should be traceable through FKs. Mark nullable vs required for each attribute.
• Document cardinality from both directions. "A CUSTOMER places zero or many ORDERs" AND "An ORDER is placed by exactly one CUSTOMER." Both sides matter.
• Use Mermaid for ERDs in documentation. Mermaid ERD syntax renders natively on GitHub, GitLab, and most documentation platforms, keeping your data models version-controlled alongside code.
• Validate with sample data. After drawing an ERD, populate it with 3-5 rows of realistic sample data per entity. This quickly reveals modeling errors.
• Separate read models from write models. In CQRS or event-sourced systems, the write-side schema should be normalized, while the read-side can be denormalized for query performance.