ER Diagrams & Relationships

Before anyone builds a house, an architect draws a plan. Before anyone creates tables for a school, a hospital or a shop, a database designer draws an ER diagram (entity-relationship diagram): a picture of the things the system stores and how they connect. It is far cheaper to move a line on paper than to change a table that already holds a lakh rows.

In this lesson you will learn the parts of an ER diagram, the three kinds of relationship and crow's foot notation, draw the practice database, check a diagram against real data, and meet the tools people use. It builds on Normalisation & Table Design.

Entities, attributes and keys

An entity is a kind of thing you store data about: a customer, a product, an order. Each entity usually becomes a table, drawn as a box with its name at the top.

An attribute is a fact about an entity, such as a customer's city. Attributes become columns. Exam papers often ask about these kinds:

• A key attribute identifies each entity uniquely. It becomes the primary key, marked PK.
• A composite attribute is made of parts, like an address with street, city and PIN code. Store the parts as separate columns.
• A multivalued attribute has several values, such as a customer's phone numbers. It becomes its own table.
• A derived attribute can be calculated, such as age from the date of birth. Usually you calculate it in a query.

A foreign key (FK) is a column that points to the primary key of another table. It is how a relationship is actually stored.

Relationships and cardinality

A relationship is a link between entities: a customer places an order, an order contains products. The most important question about a relationship is its cardinality: how many of one side can link to how many of the other.

Each side also has a minimum, called optionality or participation: must there be at least one? A customer may exist with zero orders (Divya in the practice data has none), but an order must have exactly one customer. A good diagram shows both the minimum and the maximum on each end of the line.

Crow's foot notation

The most popular way to draw these rules is crow's foot notation, named after the three-pronged "many" symbol that looks like a bird's foot. Each end of a relationship line carries two marks: the inner one is the minimum, the outer one (next to the box) is the maximum. In plain text the marks are usually written like this:

A circle means "zero is allowed", a bar means "one", and the crow's foot means "many". You read a line twice, once from each end. In customers ||----o{ orders, starting at customers: "one customer places zero or many orders". Starting at orders: "each order belongs to exactly one customer". The mark you read is always the one at the far end of the line.

The practice database as a diagram

Here is the shop database drawn in text, with keys marked. Read each line in both directions:

+-----------------+          +-----------------+          +-----------------+          +-----------------+
| customers       |          | orders          |          | order_items     |          | products        |
+-----------------+          +-----------------+          +-----------------+          +-----------------+
| PK customer_id  |-||----o{-| PK order_id     |-||----|{-| PK item_id      |-}o----||-| PK product_id   |
|    first_name   |          | FK customer_id  |          | FK order_id     |          |    product_name |
|    last_name    |          |    order_date   |          | FK product_id   |          |    category     |
|    city         |          |    status       |          |    quantity     |          |    price        |
|    state        |          +-----------------+          |    unit_price   |          |    stock        |
|    email        |                                       +-----------------+          +-----------------+
|    joined_on    |
+-----------------+

• Customers to orders: one customer places zero or many orders; each order has exactly one customer.
• Orders to order items: one order contains one or many items; each item belongs to exactly one order.
• Products to order items: one product appears in zero or many order items; each item is exactly one product.

The last two lines together form the many-to-many relationship between orders and products. order_items is the junction table, and it has attributes of its own, quantity and unit_price, which belong to neither an order nor a product alone, only to the pair.

The employees table has a relationship with itself, called a recursive or self-referencing relationship:

+-----------------+
| employees       |
+-----------------+
| PK employee_id  |-|o--+
|    first_name   |     |   each employee has zero or one manager;
|    department   |     |   each manager has zero or many direct reports
| FK manager_id   |-}o--+
+-----------------+

A diagram makes promises about the data, and you can test them with SQL. This query measures the real minimum and maximum on the "many" side of three relationships:

WITH per_customer AS (
  SELECT c.customer_id, COUNT(o.order_id) AS orders
  FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.customer_id
  GROUP BY c.customer_id
),
per_product AS (
  SELECT p.product_id, COUNT(oi.item_id) AS order_lines
  FROM products p
  LEFT JOIN order_items oi ON oi.product_id = p.product_id
  GROUP BY p.product_id
),
per_order AS (
  SELECT o.order_id, COUNT(oi.item_id) AS items
  FROM orders o
  LEFT JOIN order_items oi ON oi.order_id = o.order_id
  GROUP BY o.order_id
)
SELECT 'orders per customer' AS relationship, MIN(orders) AS min_count, MAX(orders) AS max_count FROM per_customer
UNION ALL
SELECT 'order lines per product', MIN(order_lines), MAX(order_lines) FROM per_product
UNION ALL
SELECT 'items per order', MIN(items), MAX(items) FROM per_order;

relationship            | min_count | max_count
------------------------+-----------+----------
orders per customer     | 0         | 3
order lines per product | 0         | 3
items per order         | 1         | 2
(3 rows)

A minimum of 0 confirms the circles: a customer can have no orders, and a product can have no sales. Every order has at least one item, which matches the bar in "one or many". LEFT JOIN is essential here; an inner join would hide the zeros (see LEFT & RIGHT JOIN).

Building a one-to-one relationship

One-to-one relationships are the rarest. You use them to keep optional or sensitive data in a separate table, such as loyalty cards that only some customers have. Make the foreign key the primary key too, and the database guarantees "at most one card per customer":

CREATE TABLE loyalty_cards (
  customer_id INTEGER PRIMARY KEY,
  card_number VARCHAR(16) NOT NULL UNIQUE,
  points INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);

INSERT INTO loyalty_cards (customer_id, card_number, points) VALUES
  (1, 'LC-1001', 420),
  (8, 'LC-1002', 1350);

SELECT c.first_name, c.city, l.card_number, l.points
FROM customers c
LEFT JOIN loyalty_cards l ON l.customer_id = c.customer_id
WHERE c.customer_id IN (1, 2, 8)
ORDER BY c.customer_id;

first_name | city   | card_number | points
-----------+--------+-------------+-------
Aarav      | Rohtak | LC-1001     | 420
Priya      | Jaipur | NULL        | NULL
Sneha      | Delhi  | LC-1002     | 1350
(3 rows)

In crow's foot terms this is customers ||----o| loyalty_cards: each customer has zero or one card, and each card belongs to exactly one customer.

Tools for drawing ER diagrams

You can draw on paper, but tools keep diagrams tidy and can even generate the SQL:

• MySQL Workbench (free): Database > Reverse Engineer draws the diagram of an existing database, which Workbench calls an EER (Enhanced ER) diagram; Forward Engineer turns a new model into tables.
• dbdiagram.io draws a diagram in the browser from text written in DBML.
• phpMyAdmin has a Designer view of a database's tables and links.
• draw.io (diagrams.net) is a general drawing tool with crow's foot shapes.
• Mermaid draws diagrams from text inside Markdown files, and GitHub displays them automatically.

Text-based diagrams can live in Git next to the code. Here is part of the shop in DBML and in Mermaid:

// DBML (dbdiagram.io)
Table customers {
  customer_id integer [pk]
  first_name varchar(40) [not null]
  city varchar(40) [not null]
}
Table orders {
  order_id integer [pk]
  customer_id integer [not null, ref: > customers.customer_id]
  order_date date [not null]
}

// Mermaid
erDiagram
    CUSTOMERS ||--o{ ORDERS : places
    ORDERS ||--|{ ORDER_ITEMS : contains
    PRODUCTS ||--o{ ORDER_ITEMS : "appears in"

In DBML, ref: > means "many of these point to one of those". Mermaid uses the same ||, o{ and |{ marks you learned above.

Common mistakes

• Storing a many-to-many link in one table. A product_ids column with "3,7,9" breaks first normal form. Use a junction table.
• Forgetting optionality. "A customer has many orders" hides the important detail that zero is allowed; new customers have none.
• Putting the foreign key on the wrong side. In one-to-many, the foreign key lives in the "many" table: orders.customer_id, never customers.order_id.
• Drawing derived attributes as columns. An order total can be calculated from its items; storing it creates a second copy that can go out of date.

Key points

• An ER diagram shows entities (tables), attributes (columns) and relationships (foreign keys).
• Relationships are one-to-one, one-to-many or many-to-many; many-to-many needs a junction table.
• Each end of a relationship has a minimum (zero or one) and a maximum (one or many).
• Crow's foot marks: || exactly one, o| zero or one, |{ one or many, o{ zero or many.
• Test a diagram's promises with LEFT JOIN and COUNT queries.
• MySQL Workbench can reverse engineer a diagram from a database; dbdiagram.io and Mermaid draw from text.

teachers ||----o{ courses       one teacher teaches zero or many courses
students ||----o{ enrolments    one student has zero or many enrolments
courses  ||----o{ enrolments    one course has zero or many enrolments

CREATE TABLE teachers (
  teacher_id INT PRIMARY KEY,
  full_name VARCHAR(80) NOT NULL
);

CREATE TABLE courses (
  course_id INT PRIMARY KEY,
  title VARCHAR(80) NOT NULL,
  teacher_id INT NOT NULL,
  FOREIGN KEY (teacher_id) REFERENCES teachers (teacher_id)
);

CREATE TABLE students (
  student_id INT PRIMARY KEY,
  full_name VARCHAR(80) NOT NULL
);

CREATE TABLE enrolments (
  student_id INT NOT NULL,
  course_id INT NOT NULL,
  enrolled_on DATE NOT NULL,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES students (student_id),
  FOREIGN KEY (course_id) REFERENCES courses (course_id)
);