An entity relationship diagram is a map of your data drawn before you build it. It shows the things you store, the facts about them, and how they connect, all on one page you can read in seconds and change in minutes. Get the ERD right and the tables almost write themselves.
Every relational database starts as an idea about the real world: customers place orders, students take courses, invoices contain line items. An ERD is the standard way to capture that idea visually before you commit a single CREATE TABLE. It is a shared language between the person who understands the business and the person who builds the schema, and it long predates any specific SQL engine. If you want the formal history and theory, the entity relationship model reference is the canonical starting point. This guide is the practical version: what the boxes and lines mean, and how to turn them into real tables.
What this guide covers
1. What an ERD is and why it helps
An entity relationship diagram represents the structure of a database as a set of boxes joined by lines. Each box is an entity (a thing you store), the text inside it lists attributes (facts about that thing), and each line is a relationship (how two entities connect). The endpoints of the line carry small symbols that state how many of one entity can relate to how many of the other. That is the entire vocabulary, and it is enough to describe almost any transactional system.
The reason to draw one before building is economic. Moving a box on a diagram costs a minute; moving a column in a live table costs a migration, a code change, and a deployment across every query that assumed the old shape. An ERD also forces conversations early: when you draw a line between customer and order, you have to decide whether a customer can exist with zero orders. Those questions surface business rules that plain prose tends to hide.
- It is a communication tool. A non technical stakeholder can look at the boxes and confirm the model matches reality.
- It is a design tool. Cardinality on each line tells you exactly where foreign keys go and when you need an extra table.
- It is documentation. Months later the diagram explains the schema faster than reading a hundred lines of DDL.
ERDs pair naturally with the wider design workflow. If you have not seen the full sequence from requirements to runnable schema, the guide on how to design a relational database walks it end to end, and the broader database concepts hub links the surrounding ideas.
2. The building blocks
Four pieces make up every ERD. Learn these and you can read any diagram, whatever tool drew it.
Entities
An entity is a thing worth storing separately, and it becomes a table. Good entities are nouns from the business: student, course, invoice, product. A useful test is whether you would ever want a list of them on their own. You would list students; you would not keep a separate list of "the letter grade B", so a grade is an attribute, not an entity.
Attributes
An attribute is a single fact about an entity, and it becomes a column. A student has a name, an email, and an enrollment date. Keep each attribute atomic: store first_name and last_name rather than cramming both into one field, and never pack a comma separated list into a single column. One fact per column is what makes filtering, sorting, and joining possible later.
Primary keys
A primary key is the attribute (or set of attributes) that uniquely identifies one row of an entity and never changes. In an ERD the key attribute is usually marked, often underlined or flagged with PK. Most tables get a surrogate key: an auto generated integer with no business meaning, which stays stable even when real world values change. The difference between keys that identify a row and keys that point at another table is worth its own read in primary key versus foreign key.
Relationships
A relationship is a meaningful link between two entities, drawn as a line. A student enrolls in a course; an order contains a product. Relationships are implemented with foreign keys: a column in one table that holds the primary key value of a row in another table, and the database enforces that the target actually exists. Every line you draw will eventually become a foreign key somewhere.
3. Cardinality and crow's foot notation
Cardinality is the number that answers "how many of one entity relate to how many of the other". There are three shapes that matter, and the most widely used way to draw them is crow's foot notation, named because the "many" end splits into three prongs that look like a bird's foot. Each end of a line carries two marks: an inner mark for the minimum (zero or one) and an outer mark for the maximum (one or many).
| Symbol at line end | Reads as | Meaning |
|---|---|---|
Single bar | | One (mandatory) | Exactly one, at least one required |
Circle then bar o| | Zero or one | Optional, at most one |
Crow's foot < | Many (mandatory) | One or more required |
Circle then crow's foot o< | Zero or many | Optional, any number |
Combine the two ends of a line and you get the relationship type. The three you will draw constantly are these:
| Relationship | Crow's foot reading | Everyday example | How it is built |
|---|---|---|---|
| One to one (1:1) | Bar to bar | A user and their single login profile | Foreign key with a UNIQUE constraint, or merge into one table |
| One to many (1:many) | Bar to crow's foot | One course, many enrollments | Foreign key on the "many" side |
| Many to many (many:many) | Crow's foot to crow's foot | Students and courses | A junction table with two foreign keys |
The one to many relationship is the workhorse of relational design. Read it out loud in both directions to be sure you have it right: "one course has many enrollments" and "each enrollment belongs to exactly one course". The foreign key always goes on the "many" side, so enrollment carries a course_id, never the other way around. One to one is rarer; when you genuinely need it, add a foreign key and make it UNIQUE so the parent cannot have two children, or simply keep the columns together in one table.
Tip: always read a relationship line from both ends before you trust it. Many design bugs are a line the author read one way while the business meant the other. If both sentences are true, the cardinality is correct.
4. Many to many and the junction table
A many to many relationship cannot be stored with a single foreign key, because each side has many of the other. A student takes many courses, and a course has many students, so there is no "one" side to hold the key. You cannot put a course_id on student without limiting each student to one course, and you cannot put a student_id on course without limiting each course to one student. Both are wrong.
The fix is a third table that sits between the two, called a junction table (also a bridge, link, or associative table). It holds one row per pairing: one row for "student 7 is in course 12". Each row carries a foreign key to each parent, and together those two foreign keys form the primary key, which guarantees the same pairing cannot appear twice. In crow's foot terms, the single many to many line becomes two one to many lines that both point at the junction table in the middle.
The junction table is also the natural home for facts about the pairing itself. An enrollment is not just a link; it has a grade and an enrollment date. Those attributes belong to the relationship, not to the student or the course alone, so they live on the junction row. This is a common sign that a junction table has grown into a full entity of its own worth.
-- one junction row per (student, course) pairing
CREATE TABLE enrollments (
student_id BIGINT NOT NULL REFERENCES students(id),
course_id BIGINT NOT NULL REFERENCES courses(id),
enrolled_on DATE NOT NULL DEFAULT CURRENT_DATE,
grade CHAR(2),
PRIMARY KEY (student_id, course_id)
);5. A worked example: students and courses
Take a small college registration system and read it as an ERD in words. There are two obvious entities. A student box holds an id (the primary key), a name, and an email. A course box holds an id, a title, and a credit count. Between them runs a single line with a crow's foot on both ends: a student enrolls in many courses, and a course enrolls many students. That is a many to many relationship, so from section four we know it needs a junction table.
We introduce an enrollment box in the middle. The original many to many line becomes two one to many lines: one from student to enrollment (one student has many enrollments), and one from course to enrollment (one course has many enrollments). The enrollment box also carries its own attributes, the date and the grade, because those facts describe the pairing rather than either parent. Described that way, the diagram maps directly onto three tables.
CREATE TABLE students (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(120) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE
);
CREATE TABLE courses (
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title VARCHAR(160) NOT NULL,
credits INT NOT NULL CHECK (credits > 0)
);
CREATE TABLE enrollments (
student_id BIGINT NOT NULL REFERENCES students(id) ON DELETE CASCADE,
course_id BIGINT NOT NULL REFERENCES courses(id),
enrolled_on DATE NOT NULL DEFAULT CURRENT_DATE,
grade CHAR(2),
PRIMARY KEY (student_id, course_id)
);Notice how faithfully the SQL follows the diagram. Two parent entities became two tables with surrogate keys. The relationship became a junction table whose composite primary key stops a student being enrolled in the same course twice. The relationship attributes, date and grade, sit exactly where the ERD put them. With that in place, a question like "which students are in a given course" is a plain join, and the query cannot silently drop rows because every link is a real foreign key.
-- roster for one course, newest enrollments first
SELECT s.name, e.enrolled_on, e.grade
FROM enrollments e
JOIN students s ON s.id = e.student_id
WHERE e.course_id = 12
ORDER BY e.enrolled_on DESC;6. Turning an ERD into a schema
Converting a finished diagram into DDL is mechanical once you know the rules. Each part of the ERD has a fixed translation into SQL, so the same steps work for a two table sketch or a two hundred table system.
Turn each entity into a table
Every box becomes a CREATE TABLE. The entity name becomes the table name, typically a plural snake_case noun.
Turn each attribute into a column
List every attribute as a column and give it the smallest correct data type: DATE for dates, DECIMAL for money, INT for counts.
Add a primary key to every table
Use a surrogate id unless a natural key is genuinely stable and unique. Keep natural keys as a UNIQUE constraint.
Implement one to many lines with a foreign key
Put the foreign key on the "many" side, referencing the parent's primary key. Declare it with REFERENCES.
Resolve many to many lines into a junction table
Create a bridge table with a foreign key to each parent and a composite primary key across both.
Encode the minimums as constraints
A mandatory "one" end becomes NOT NULL on the foreign key. Value rules from the diagram become CHECK constraints.
Index the foreign keys
Add an index on every foreign key column you will join or filter on so reads stay fast as the tables grow.
One detail decides where the foreign key goes: the crow's foot always points at the child. Whichever end has the many symbol is the table that carries the key. Get that backwards and you invert the whole relationship. The order of creation matters too: create parent tables before children so the REFERENCES targets already exist. For the exact syntax each engine accepts, the PostgreSQL data definition documentation is an excellent, precise reference that transfers well to other databases.
Before you finalize the schema, run it past normalization. An ERD can still hide repeated or derivable data, and normalizing removes it so each fact lives in exactly one place. The full ruleset is in database normalization explained. The chart below shows, in representative terms, where the risk of an expensive later change concentrates across ERD decisions.
Read it as a schedule of regret. Cardinality and junction tables sit at the top because fixing them means restructuring live data and rewriting queries, while a missing index can be added on a running system in seconds. Spend your care on the structural ends of the lines.
7. Tools to draw ERDs
You do not need special software to design an ERD; pen and paper is fine for a first pass, and many experienced designers start there precisely because it is fast to erase. When you want something shareable or something that generates SQL, plenty of tools exist across the free and paid spectrum.
- Text based tools let you type the entities and relationships and render the diagram automatically, which keeps the model in version control alongside your code.
- Drag and drop diagram tools give you boxes and crow's foot connectors on a canvas, good for workshops and stakeholder reviews.
- Database aware modelers reverse engineer an ERD from an existing database and can forward engineer a diagram into DDL, useful when you inherit a schema with no documentation.
The tool matters far less than the discipline. A clear diagram on a napkin beats a beautiful one that models the wrong relationships. Pick whatever lets you change the model quickly, because the whole point of an ERD is that changing it is cheap.
8. Common mistakes to avoid
The same handful of errors show up in ERDs again and again. Knowing them in advance is the fastest way to review your own diagram.
| Mistake | Why it hurts | Fix |
|---|---|---|
| Leaving a many to many unresolved | No single foreign key can store it, so data ends up duplicated or lost | Add a junction table with two foreign keys |
| Cardinality read one way | The foreign key lands on the wrong side and inverts the relationship | Read every line aloud in both directions |
| Multi valued attributes | A comma separated column cannot be filtered, joined, or counted | Split into its own related table |
| Using a natural key that changes | An email or phone number as the key breaks every reference when it changes | Use a surrogate id, keep the natural value UNIQUE |
Watch out: the single most common ERD bug is treating a many to many as a one to many. If both entities can have many of the other, you need a junction table, full stop. Storing a list in one column will pass a demo and fail in production the moment you need to query it.
An ERD is a small investment that pays off every day the database lives. Draw the boxes, name the attributes, mark the keys, read every line in both directions, and resolve every many to many before you write a line of DDL. Do that and the schema, and the queries built on it, tend to be almost boring, which is exactly what you want. To build the skills that turn a clean diagram into a production model, the SQL intermediate course covers keys, joins, and schema design with exercises at every step, and the guide on building enterprise databases with SQL shows the same principles at large scale.