Entity-relationship diagrams

01The problem

A new analyst at Harbourline is asked: "How much has each industry paid us?" The answer needs three tables: industry is on customers, payments are on payments, and the only connection between them is shipments. Without a map, the analyst guesses at joins. With an entity-relationship diagram (ERD), the path is visible in seconds.

02The concept

An ERD draws a data model:

• a box per entity (table), listing its attributes (columns), with keys marked;
• a line per relationship, from a primary key to the foreign keys that point at it;
• line endings showing cardinality (crow's-foot notation).

Here is Harbourline's full ERD:

Reading one line, both ways. Take the line between customers and shipments:

• From customers: one customer books zero or many shipments (circle + crow's foot at the shipments end).
• From shipments: each shipment is booked by exactly one customer (two bars at the customers end).

A line back to the same table. employees.manager_id points to employees.employee_id: a self-relationship (each employee reports to zero or one manager). This is how organisation charts are stored.

Tools for drawing ERDs: draw.io (diagrams.net, free), dbdiagram.io, Lucidchart, Microsoft Visio, or the diagram features in SSMS, MySQL Workbench and Power BI's Model view. Paper works too; the notation matters more than the tool.

03Example

"How much has each industry paid us?" On the diagram, walk from customers (industry) → shipments (via customer_id) → payments (via shipment_id). Each step is one JOIN:

SELECT c.industry, SUM(p.amount) AS total_paid
FROM customers AS c
JOIN shipments AS s ON s.customer_id = c.customer_id
JOIN payments  AS p ON p.shipment_id = s.shipment_id
GROUP BY c.industry
ORDER BY total_paid DESC;

04Walkthrough

Drawing an ERD from scratch, for Ashgrove Chambers:

1. Boxes: clients, matters, hearings, invoices.
2. Keys: client_id, matter_id, hearing_id, invoice_id as primary keys.
3. Lines: clients → matters (a client has many matters: FK matters.client_id); matters → hearings (FK hearings.matter_id); matters → invoices (FK invoices.matter_id).
4. Endings: a matter must have one client (bars); a client may have zero matters (circle, crow's foot); a matter may have zero hearings (non-litigation work like contract review never goes to court).
5. Check with a question: "Overdue amount per client" → clients → matters → invoices. Two joins, no gaps. ✓

You'll draw this ERD properly in the final project.

05Practice

06Check your understanding