The Complete ER Diagram Guide

Every project that stores data eventually runs into the same three questions: what are we keeping track of, what do we need to know about each of those things, and how do they connect? An entity relationship diagram is how a team answers all three on one page, before a single table exists.

What an ER Diagram Actually Is

Quick answer. An ER (entity–relationship) diagram is a picture of the data your project needs to store. It shows the things you keep track of (entities), the facts you record about them (attributes), and how those things connect (relationships). For a college project, you draw it before writing any database code, so your tables become a direct copy of a design you can explain.

• Entities – the real things the system stores information about, such as a member, a book, or an order.
• Attributes – the individual facts recorded about each entity, such as a title, an email address, or a due date.
• Relationships – how those entities connect, and how many of each are involved.

That narrow focus is the point. An ERD is deliberately not a picture of your code, your screens, or your server layout. It is a picture of meaning: what the business considers a distinct thing, and what it considers a fact about that thing. Because it stays at that level, a project manager who has never written a query can read one and correctly object that something is missing.

What an ERD is not

Three distinctions clear up most beginner confusion:

• It is not a finished database schema. A schema specifies data types, lengths, constraints, defaults and indexes. An ERD stops at due_date without deciding whether that is a DATE or a DATETIME. The ERD comes first and the schema is derived from it. (See [ER Diagram vs Database Schema] — [INTERNAL LINK].)
• It is not a UML class diagram. Classes carry behaviour: methods, inheritance, visibility. Entities carry only data.
• It is not a flowchart. A flowchart shows the order in which things happen. An ERD shows what exists at rest, with no sequence implied.

Why Teams Draw an ERD Before Writing Any Code

The practical value of an ERD is that it forces vague requirements to become specific decisions while those decisions are still cheap to change. Renaming a box on a diagram costs a minute. Restructuring three tables after six weeks of development costs considerably more.

The clearest way to see this is to trace how a sentence from a client turns into a structural decision and then into a capability the finished system either has or does not have.

Requirement	ERD decision	Result
“A member should be able to borrow several books, and we need the history.”	Create a separate LOAN entity between MEMBER and BOOK instead of a “borrowed by” attribute on the book.	Every borrowing keeps its own dates. Past loans survive after the book is returned, so history and reporting are possible.
“We need to know who wrote each book.”	Create an AUTHOR entity linked to BOOK, rather than an author name typed into every book row.	An author’s details are stored once. Correcting a misspelled name fixes it everywhere, and “all books by this author” is a straightforward lookup.
“Staff must be able to see what is overdue today.”	Put due_date and returned_on on LOAN.	Overdue becomes a simple comparison against today’s date. Had the dates lived on BOOK, the question would have been unanswerable.

Notice that in each row the requirement was written in ordinary language and the decision was structural. That translation is the actual work an ER diagram does. Beyond it, a diagram buys you a few things that are hard to get any other way:

• A shared vocabulary. Once the diagram says MEMBER, the team stops drifting between “user”, “customer” and “borrower” in conversation, tickets and code.
• Visible gaps. Missing information is much easier to spot as a missing line on a diagram than as an absent paragraph in a document.
• Review by non-developers. A librarian can look at the diagram and say “a book can have two authors”, which is exactly the kind of correction you want before implementation.
• Faster onboarding. A new developer understands a database far quicker from one diagram than from reading forty table definitions.
• Change impact at a glance. Before adding a feature, the diagram shows what it touches.

When and Where ER Diagrams Are Useful

ERDs earn their keep in five situations in particular:

• Starting a new project. The diagram is the bridge between the requirements document and the first migration file.
• Inheriting an undocumented database. Drawing the ERD from existing tables (most tools can generate a draft) is often the fastest route to understanding a legacy system.
• Talking to clients and stakeholders. Non-technical people can validate a diagram; they cannot validate DDL.
• Handover and documentation. A current ERD kept alongside the code answers most “how does this fit together” questions without a meeting.
• Coursework, interviews and design exercises. Data modelling questions are common precisely because a small ERD reveals whether someone can think in structure.

It is equally worth knowing when to skip one. A single-table script, a throwaway prototype, or a cache with two key patterns does not need a diagram. Model when there is genuine structure to reason about.

The Elements of an ER Diagram

Entity

What it is. An entity is a category of thing the system stores data about: a member, a book, an invoice, a course. In the diagram it is drawn as a rectangle and named with a singular noun.

Why it matters. Entities become tables. Choosing them well is the single decision that most shapes what the finished system can and cannot do, because every later question you ask the database is a question about entities and how they relate.

How to test a candidate. Ask two questions. Can you list several distinct examples of it? Do you need to store more than one fact about each example? A book passes both: you can name hundreds of them, and you want the title, the ISBN and the year. A “library” fails the first if the system serves one branch, because there is only ever one, so it becomes an assumption rather than a table.

The confusion to clear up early. An ERD shows entity types, not entity instances. The box labelled BOOK stands for the concept of a book, not for a particular copy of Clean Code. Beginners often try to draw one box per example, which produces an unusable diagram. One box covers every row that will ever exist.

Attribute

What it is. An attribute is a single fact about an entity. title, isbn and published_year are attributes of BOOK. In classic Chen notation each attribute is drawn as an oval joined to its entity; in the compact style used by most modern tools they are listed as rows inside the entity box, which is far more readable once a diagram has more than three entities.

Why it matters. Attributes define the questions your system will be able to answer. If joined_on is not recorded on MEMBER, no amount of clever code will later tell you how many members signed up last quarter. Attribute selection is requirement capture in disguise.

The four variations worth knowing:

• Simple — one indivisible value, such as isbn.
• Composite — a value that can sensibly be split, such as full_name into first and last, or an address into street, city and postcode. Split it when you will need the parts separately, and only then.
• Derived — a value calculable from another, such as age from a date of birth. Usually stored as the source fact and computed on demand.
• Multivalued — a fact that can occur several times for one instance, such as several phone numbers for one member. This is the useful one, because a multivalued attribute is almost always a hidden entity announcing itself.

The confusion to clear up early: is this an attribute or an entity? Two tests settle it. First, do you need to store facts about this thing? An author has a country and a date of birth, so an author is an entity, not a text field on the book. Second, will the same value repeat across many rows? If a hundred books each store the text “Robert C. Martin”, that repetition is the signal to promote it to an entity.

Keys

What they are. A primary key is the attribute that uniquely identifies one instance of an entity: given a member_id, exactly one member is meant. A foreign key is an attribute in one entity that holds the primary key of another, and it is the mechanism by which a relationship actually exists in the database. A composite key uses two or more attributes together when no single one is unique on its own.

Why they matter in an introductory ERD. Without keys, a relationship line is only a claim. The key is what makes it real. Marking keys is also the point where the diagram becomes directly implementable, which is why crow’s foot diagrams almost always show PK and FK markers even though they are strictly a schema concern.

Natural versus surrogate. A natural key is a real-world identifier such as an ISBN. A surrogate key is a meaningless generated value such as an auto-incrementing book_id. Natural keys read better; surrogate keys survive real-world messiness, since ISBNs are missing from older books and occasionally reused. Most projects use surrogate primary keys and keep the natural identifier as an ordinary attribute, which is what the example below does.

Relationship

What it is. A relationship is a meaningful association between two entities: a member borrows a book, an author writes a book. It is drawn as a diamond in Chen notation and as a plain connecting line in crow’s foot notation.

Why it matters. Relationships are where the business rules live. The line between MEMBER and LOAN is not decoration; it encodes the rule that a loan cannot exist without a member.

Name them as verbs, and read them both ways. A relationship should form a sentence in each direction: “an author writes books” and “a book is written by an author”. If you cannot phrase it naturally in both directions, the relationship is usually wrong or the entities are. Vague labels like “has” or “related to” are a warning sign, because they hide the rule instead of stating it.

Cardinality

What it is. Cardinality answers “how many?” on each side of a relationship. There are three basic forms.

How each one shows up in practice. A 1:1 relationship is comparatively rare and often means the two entities could be one, unless you are separating rarely-used or sensitive data. A 1:N relationship is the workhorse of nearly every database, and it is implemented by putting a foreign key on the “many” side. An M:N relationship cannot be implemented directly with two tables, so it is resolved into two 1:N relationships by introducing a third entity in the middle. The example later in this article does exactly that, and [Cardinality in ER Diagrams Explained] — [INTERNAL LINK] goes further into the topic.

Participation, briefly. Alongside “how many” sits “is it required”. A loan must have a member, so a loan’s participation is mandatory. A member need never borrow anything, so a member’s participation is optional. Crow’s foot notation encodes this with a small ring for optional and a short bar for mandatory, which is why the symbols come in pairs.

ERD Symbols and Notation

There is no single universal notation. Two are worth recognising.

Chen notation is the original academic style: rectangles for entities, ovals for attributes, diamonds for relationships, with numbers or letters on the lines for cardinality. It is explicit and excellent for teaching, but it spreads out quickly and becomes unwieldy past a handful of entities.

Crow’s foot notation puts attributes inside the entity box and expresses cardinality with line-end symbols. It is compact, it is what nearly every modern diagramming tool produces, and it is what you will meet in professional work. This article uses it for the worked example.

The four crow’s foot line ends are worth memorising, because once you know them you can read almost any professional ERD without a legend.

Tool conventions vary in small ways, and some teams add colour or extra markers. [ER Diagram Symbols & Notation] — [INTERNAL LINK] covers the full symbol set including the variants you will meet in specific tools.

How to Read an ER Diagram

Reading an ERD is a mechanical procedure, and doing it deliberately is the fastest way to become fluent:

1. Read the entity names first, ignoring every line. They tell you what the system is about before any detail intrudes.
2. Pick one entity and follow each line leaving it. Take them one at a time.
3. Say the sentence out loud, using the relationship label. “One author writes…”
4. Finish the sentence with the symbol at the far end. A crow’s foot with a ring means “…zero or many books.”
5. Now read it backwards. “Each book is written by exactly one author.” If either direction sounds wrong to someone who knows the business, you have found a modelling error.
6. Only then look at the attributes, checking that each fact sits on the entity it genuinely describes.

Step five is where most real problems surface. A diagram can be technically valid and still say something false about the world, and reading it aloud in both directions is the cheapest way to catch that.

The Basic Process for Creating One

The workflow below is deliberately linear for a first diagram. In practice you will loop back as new questions surface, which is normal and is exactly why you draw before you build.

1. Write down the requirement in plain sentences.
2. Underline the nouns; they are your entity candidates.
3. Filter that list, dropping anything that is really an attribute or a one-off constant.
4. List the facts you need about each surviving entity.
5. Choose a primary key for each.
6. Underline the verbs connecting your nouns; they are your relationships.
7. Decide cardinality and participation for each relationship, and resolve any many-to-many into a middle entity.
8. Place foreign keys, draw the diagram, then read it back aloud.

[How to Create an ER Diagram Step by Step] — [INTERNAL LINK] works through this process in more depth, including how to handle requirements that contradict each other.

A Worked Example: A Small Library System

Here is the brief, written the way a client would actually say it:

We run a small library. We need to record the books we hold, along with who wrote each one. People register as members and can borrow books. For every borrowing we need to know which book, which member, when it went out, when it is due back, and whether it has been returned yet. A member can borrow several books over time, and we want to keep the history even after a book comes back.

Step 1: Identify the entities

The nouns are: library, book, author, member, borrowing. Filtering that list:

• Library is dropped. There is exactly one, so it is context rather than data.
• Book stays. Many of them, several facts about each.
• Author stays. It could have been a text field on BOOK, but “who wrote each one” implies we care about authors as things, and repeating a name across many books invites inconsistent spellings.
• Member stays, for the same reasons.
• Borrowing stays, and becomes LOAN. This is the important one, discussed under relationships below.