ER Diagrams

Introduction

Entity-Relationship (ER) diagrams visually show entities, attributes and relationships before any SQL is written. They're the blueprint of your schema.

Understanding the topic

Core concepts to understand:

• Boxes = entities. Lines = relationships. Symbols = cardinality.
• Crow's-foot notation is the most common.
• Tools: dbdiagram.io, drawSQL, Lucidchart, Excalidraw.
• Iterate on the diagram with the team before coding.
• Generate SQL from the diagram in many tools.

Syntax reference

Visual workflow / architecture:

┌──────────┐ 1   *   ┌──────────┐ *   * ┌──────────┐
│  USERS   │────────▶│  ORDERS  │───────▶│ PRODUCTS │
│ id       │         │ id       │   ▲    │ id       │
│ email    │         │ user_id  │   │    │ name     │
│ name     │         │ total    │   │    │ price    │
└──────────┘         └────┬─────┘   │    └──────────┘
                          │ 1       │ *
                          ▼
                  ┌────────────────┐
                  │  ORDER_ITEMS   │
                  │ order_id  FK   │
                  │ product_id FK  │
                  │ qty, price     │
                  └────────────────┘

Real-world use

Every well-run team starts new features with an ER diagram review — it surfaces design flaws cheaply, before a single migration is run.

Best practices

• Diagram first, code second.
• Use crow's-foot notation for clarity.
• Keep the diagram updated as the schema evolves.

Hands-on exercise

Interview preparation — practice these questions:

• Q1. What does crow's-foot notation show?
• Q2. Why diagram before coding?
• Q3. Tools you've used for ER diagrams.
• Q4. How to model M:N visually?
• Q5. Difference between conceptual and physical ER models.