Drawing Entity Relationship Diagrams

Someone hands you a paragraph: “Members borrow books. A book can have several authors. We need to know when each book is due back.” Before you write a single CREATE TABLE, you need to know which tables there are and how they connect. Get it wrong, and you find out three months later when a book with two authors won’t fit. An entity relationship diagram (ERD) is a drawing of that answer. Boxes are the things you store, lines are how they relate, and small symbols at the ends of each line say how many. It is a map of the data’s structure. It is not a flowchart: a flowchart shows steps happening over time, and an ERD shows what exists and how it is connected. This post teaches you to draw one yourself. You will learn to read the symbols, follow a five-step recipe on a small library, fix the four most common mistakes, and then try one on your own. It helps to know what a table, a primary key and a foreign key are. If not, read The Relational Model first.

In short

An ERD has four parts: entities (boxes), attributes (lines of text inside each box), relationships (lines between boxes) and cardinality (the symbols at each end).
Read across the line. The symbol next to a box answers “how many of this box for one of the other?” The inner symbol is the maximum, the outer one is the minimum.
Recipe: find the nouns → pick the entities → add attributes and keys → draw lines for the verbs → set the cardinality.
Many-to-many (crow’s feet on both ends) needs a bridge table.
The foreign key goes on the crow’s-foot side. For one-to-one it goes on the optional side.

The four parts

Take this sentence: “Each member places zero or more loans. Each loan belongs to exactly one member.” Every ERD is built from four kinds of part, and this sentence has all of them.

Entity: a kind of thing you store many of. The nouns member and loan become boxes, named in the singular: Member and Loan.
Attribute: a fact about one entity, such as a member’s name. It is written as a line of text inside the box. (This sentence has none yet.)
Relationship: how two entities are linked. The verb places becomes a line between the boxes, labelled with the verb.
Cardinality: how many on each side. Zero or more and exactly one become symbols at the two ends of the line.

An entity is a kind of thing (Member), not one thing (Ana). Ana is one row in the Member table later on. Name boxes in the singular, because each box describes one row.

Reading crow’s-foot notation

There are several ways to draw cardinality. This post uses crow’s foot, the style most developer tools use: dbdiagram.io, Mermaid, DBeaver, MySQL Workbench and Lucidchart all draw it. Each end of a line carries two symbols:

Inner symbol, next to the box: the maximum. A bar | means one. A crow’s foot < (three prongs) means many.
Outer symbol, further along the line: the minimum. A bar | means one, so the link is required. A circle o means zero, so it is optional.

To read a line, start at one box, cross the line, and read the symbol at the far end. Start at Member and read the symbol next to Loan: “Each member has zero or more loans.” Then start at Loan and read the symbol next to Member: “Each loan belongs to exactly one member.” Try other symbols and see which real situations each one allows. A trick to remember the minimum: the circle looks like a zero. The bar looks like a one. Some tools write the same rules as numbers instead, in UML style: 1 (exactly one), 0..1 (zero or one), 1..* (one or more), 0..* (zero or more). The older Chen notation, from Peter Chen’s 1976 paper that introduced ER modelling, draws relationships as diamonds and attributes as ovals. It is good for teaching concepts, but it takes a lot of space, so developer tools rarely use it. The ideas are the same in all three styles. Only the symbols change.

The recipe: from a brief to a diagram

Here is a short brief for a library system. You will rarely get a cleaner one. Follow five passes over it. Each pass looks for one kind of word and adds one kind of part to the drawing. A few rules of thumb for each step:

Nouns. Mark them all, even the obvious ones. It is faster to cross a noun off than to notice one you missed.
Entities. Keep a noun if there are many of it and it has facts of its own. Drop the system itself (“the library”, “the app”). Watch for verbs that carry facts: “borrows” has dates, so it becomes the Loan entity. Orders, bookings, payments and enrolments are often found this way.
Attributes. A noun that only describes something else is an attribute. Give every entity a primary key that never changes. A plain ID such as memberID is the safe default.
Relationships. One line per pair of entities that the brief links directly. Label it with the verb, so the line reads as a sentence: Member makes Loan.
Cardinality. For each line, ask the question twice: “One member has how many loans?” and “One loan has how many members?” Draw each answer next to the box being counted. If the brief doesn’t say, ask whoever wrote it. That question is often the most useful thing the diagram does.

Notice that the diagram has no foreign keys yet. That is on purpose. At this stage the drawing is about meaning. Foreign keys come next, and the lines tell you where they go.

Many-to-many needs a bridge

Look at the Book–Author line. It has a crow’s foot on both ends: a book has one or more authors, and an author writes one or more books. Lines like this can’t be stored as drawn. Try with real data: Good Omens was written by Terry Pratchett and Neil Gaiman. A Book row has one authorID cell, so a second author needs a second row, and that row repeats the book’s primary key. The fix is a third table, often called a bridge, junction or associative table. It has one row per book–author pair, and both of its columns are foreign keys. In the diagram, the bridge turns one many-to-many line into two one-to-many lines, with the crow’s feet pointing at the bridge. The bridge can also hold facts about the pair itself. For example, BookAuthor.position would store whether Gaiman is listed first or second. You have already met one of these. Loan sits between Member and Book, and over time a member borrows many books and a book is borrowed by many members. Loan is a bridge with its own facts (the dates), which is why it showed up as an entity in step 2.

Where the foreign key goes

Once every line is one-to-many or one-to-one, the diagram tells you exactly where each foreign key goes. The minimum symbols tell you whether it can be empty. The rule in one line: the foreign key lives where the crow’s foot is. Each loan has one member, so the loan can store it. A member has many loans, so the member can’t. The minimums map to SQL too, but only partly:

Minimum on the diagram	What SQL can enforce
Bar next to the “one” side (every loan must have a member)	`NOT NULL` on the foreign key
Circle next to the “one” side (a loan may have no member)	leave the foreign key nullable
Circle next to the “many” side (a member may have no loans)	nothing to do; that is the default
Bar next to the “many” side (every book has at least one author)	no plain constraint does this; check it in the app, a trigger or a transaction

That last row surprises people. A foreign key can say “this loan points at a real member”. It can’t say “this book must have at least one row in another table”. Draw the rule anyway, because the diagram is where you agree on it. Then decide who enforces it.

Four common mistakes

These four show up in nearly every first ERD. Each card shows the mistake, and one click shows the fix. The fourth mistake is the most common one. It happens when you draw the symbol next to the box you start from, instead of the box you are counting. When in doubt, read the line out loud from both ends.

Your turn

Here is a new brief. Answer the four questions, then check your answers. If you get stuck, scroll back to the recipe.

From diagram to code

Most teams don’t draw ERDs by hand any more. They write them as text, so the diagram lives next to the code and shows up in code review. Here is the finished library, with the bridge table and every foreign key, in three common formats. Mermaid. GitHub, GitLab, Notion and this site render it straight from Markdown: DBML. This is the format of dbdiagram.io, which draws the diagram and exports SQL:

Table Member {
  memberID int [pk]
  name varchar [not null]
  email varchar [not null, unique]
}

Table Book {
  bookID int [pk]
  title varchar [not null]
  isbn varchar [unique]
}

Table Author {
  authorID int [pk]
  name varchar [not null]
}

Table Loan {
  loanID int [pk]
  memberID int [not null, ref: > Member.memberID]
  bookID int [not null, ref: > Book.bookID]
  borrowedOn date [not null]
  dueOn date [not null]
}

Table BookAuthor {
  bookID int [ref: > Book.bookID]
  authorID int [ref: > Author.authorID]
  indexes {
    (bookID, authorID) [pk]
  }
}

SQL. This is what the database actually runs:

CREATE TABLE Member (
  memberID INT PRIMARY KEY,
  name     VARCHAR(100) NOT NULL,
  email    VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE Book (
  bookID INT PRIMARY KEY,
  title  VARCHAR(200) NOT NULL,
  isbn   VARCHAR(17) UNIQUE
);

CREATE TABLE Author (
  authorID INT PRIMARY KEY,
  name     VARCHAR(100) NOT NULL
);

CREATE TABLE Loan (
  loanID     INT PRIMARY KEY,
  memberID   INT NOT NULL REFERENCES Member(memberID),
  bookID     INT NOT NULL REFERENCES Book(bookID),
  borrowedOn DATE NOT NULL,
  dueOn      DATE NOT NULL
);

CREATE TABLE BookAuthor (
  bookID   INT NOT NULL REFERENCES Book(bookID),
  authorID INT NOT NULL REFERENCES Author(authorID),
  PRIMARY KEY (bookID, authorID)
);

You can go the other way too. DBeaver, DataGrip, MySQL Workbench and pgAdmin can draw an ERD from an existing database’s foreign keys. That is a quick way to learn a codebase you didn’t design.

How much detail?

The same model can be drawn at three levels of detail. Pick the level that suits the reader.

Conceptual: entity names and lines, maybe cardinality. Use it to agree on scope with people who don’t write code. In this post: recipe steps 2 and 4.
Logical: adds attributes, primary keys, cardinality and bridge tables. Use it to agree on the design. In this post: recipe step 5, plus the bridge.
Physical: adds column types, NOT NULL, foreign keys and indexes. Use it to build. In this post: the SQL above.

When an ERD is the wrong tool

Process questions. “What happens after a book is returned?” is a flow, so draw a flowchart or sequence diagram. An ERD has no time and no order.
Data with no fixed shape. Free-form documents, logs or user-defined fields don’t fit fixed boxes well. A rough sketch still helps, but don’t force it.
Rules beyond cardinality. “A member can have at most five loans at once” is a real rule, but crow’s foot can’t draw it. Write it as a note next to the line.

Once the diagram is drawn, check each table against the normal forms in Designing a Relational Database. That catches the facts that are still stored twice.

Check yourself

Glossary

Term	Meaning
ERD	Entity relationship diagram: a drawing of what data is stored and how it connects.
Entity	A kind of thing stored many times, drawn as a box. Becomes a table.
Attribute	A fact about one entity, drawn inside its box. Becomes a column.
Relationship	A link between two entities, drawn as a labelled line.
Cardinality	How many of one entity relate to one of the other: the end symbols.
Crow’s foot	The three-pronged end symbol meaning “many”; also the name of the notation.
Minimum / maximum	The outer (circle = 0, bar = 1) and inner (bar = 1, crow’s foot = many) symbols at a line’s end.
One-to-many	One row on one side, many on the other. Foreign key on the many side.
Many-to-many	Many on both sides. Needs a bridge table.
Bridge table	A table with one row per related pair, keyed by both foreign keys. Also called a junction or associative table.
Primary key (PK)	The column(s) that pick out exactly one row.
Foreign key (FK)	A column whose value must match a primary key in another table.
Conceptual / logical / physical model	The same design at three levels of detail, from names only to full SQL.

For AI agents

Adapted from Lucidchart, “What is an Entity Relationship Diagram (ERD)?”. Changes: an ERD is described here as a structure diagram, not a type of flowchart; product-specific steps and the history before Chen’s 1976 paper are left out; the rule that “one or more” can’t be enforced by a plain foreign key is added. The library and clinic data are illustrative; figures are simplified teaching models.

All posts

Frontend

Database

Drawing Entity Relationship Diagrams

The four parts

Reading crow’s-foot notation

The recipe: from a brief to a diagram

Many-to-many needs a bridge

Where the foreign key goes

Four common mistakes

Your turn

From diagram to code

How much detail?

When an ERD is the wrong tool

Check yourself

Glossary

For AI agents

​The four parts

​Reading crow’s-foot notation

​The recipe: from a brief to a diagram

​Many-to-many needs a bridge

​Where the foreign key goes

​Four common mistakes

​Your turn

​From diagram to code

​How much detail?

​When an ERD is the wrong tool

​Check yourself

​Glossary

​For AI agents

The four parts

Reading crow’s-foot notation

The recipe: from a brief to a diagram

Many-to-many needs a bridge

Where the foreign key goes

Four common mistakes

Your turn

From diagram to code

How much detail?

When an ERD is the wrong tool

Check yourself

Glossary

For AI agents