What Is an ER Diagram?

An ER diagram (entity-relationship diagram) is a drawing of a database: which pieces of information exist, what each piece is made of, and how the pieces refer to each other. Developers use it to design a schema before writing a single CREATE TABLE, and analysts use it to document an existing database so everyone shares the same mental model.

The model dates back to Peter Chen's 1976 paper, and it has outlived every database vendor since. Tools and notations changed — crow's foot replaced Chen's diamonds in most engineering teams — but the underlying idea held: separate what you store from how you store it, then map one onto the other.

The three building blocks

• Entity — a thing you store data about: a customer, an order, a product. In the diagram it is a rectangle (a table in the database).
• Attribute — a property of that thing: an email, a price, a created_at date. Attributes are the columns.
• Relationship — how two entities are connected: a customer places orders, an order contains products. Relationships become foreign keys.

Keys: how rows stay unique and connected

Every entity needs a primary key (PK) — a column, or a combination of columns, that identifies exactly one row. When one entity needs to point at another, it stores the other entity's primary key in a foreign key (FK) column. That single pattern is what the entire diagram expresses: an edge from users to posts with a crow's foot at the posts end means posts.user_id references users.id and many posts can belong to one user.

Some keys identify a row without being the primary key. A UNIQUE constraint on users.email is an alternate key: nothing in the diagram's structure changes, but the badge next to the column tells a reader that duplicates are impossible. Composite keys — two or more columns jointly unique — show up as both columns badged PK, and they are common in join tables.

Cardinality, in one paragraph

Relationships come in three shapes. One-to-one (1:1): each row on one side matches at most one row on the other, like a user and their settings. One-to-many (1:N): one row matches many — one customer, many orders. Many-to-many (N:M): many on both sides — posts and tags. Databases cannot store N:M directly; you add a join table in the middle, which turns it into two 1:N relationships.

Notation: crow's foot in practice

Most modern tools draw crow's foot notation. The symbols sit at the ends of each edge: a single tick means exactly one, a crow's foot (three prongs) means many, a circle means zero is allowed. So "users ||—o{ posts" reads as: every post belongs to exactly one user, and a user may have zero or more posts. Chen notation, which you will still meet in academic material, draws entities as rectangles and relationships as diamonds between them — same content, different shape. Reading the end markers is the whole skill: the line says "these tables are connected", the markers say whether deleting one row orphans another.

A minimal example

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  title VARCHAR(200) NOT NULL
);

The diagram for these two tables would show a users box and a posts box connected by a line labeled 1:N. You can paste the SQL above into ERDGenerator and see that exact diagram — then drag the tables around, rename a column and export it back.

A fuller example: posts and tags

Add tagging to the blog above and you hit the case that makes ER diagrams earn their keep. A post can carry many tags, a tag can be applied to many posts — that is N:M, and it needs a join table. In the diagram you draw three boxes, not two, and the join table is often the place where a relationship acquires attributes of its own (who tagged, when):

CREATE TABLE tags (
  id SERIAL PRIMARY KEY,
  name VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE post_tags (
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  tag_id INTEGER NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
  PRIMARY KEY (post_id, tag_id)
);

The composite primary key (post_id, tag_id) does two jobs at once: it identifies each pairing, and it guarantees the same tag cannot be attached to the same post twice. Reading the diagram, you can spot a join table instantly — its primary key is made of foreign keys.

When the diagram is worth drawing

• Design review: a five-minute look at the diagram catches a missing foreign key or a wrong cardinality before it becomes a migration.
• Onboarding: a new developer reading 40 CREATE TABLE statements builds a mental model in hours; one diagram gets them there in minutes.
• Refactoring: when you plan to split a table or extract a service, you draw the target schema first and argue about the picture, not the code.
• On-call documentation: at 3 a.m., nobody wants to reverse-engineer relationships from column names.

Common mistakes

• Drawing the UI, not the data. "Screens" and "pages" are not entities; only things the database must remember are.
• Confusing a relationship with an attribute. "order has a customer_email" stored as a string is a denormalized copy of a relationship — the diagram makes that visible.
• Leaving out cardinality. A bare line between two boxes says almost nothing; the markers are the information.
• Hiding join tables because they feel like clutter. They are where the N:M lives; hiding them hides the truth.
• Treating the diagram as documentation of last year's schema. A diagram that drifted from the database is worse than no diagram.

Best practices

• Name relationships with verbs in both directions: a user places orders; an order is placed by one user.
• Keep one consistent naming convention (singular or plural, snake_case) across every entity — diagrams drift when names do.
• Mark nullability and uniqueness on the diagram; they are schema decisions, not implementation details.
• Generate the diagram from the schema (SQL, Prisma, DBML) rather than drawing by hand, so the two cannot drift apart.
• Draw only the tables that matter for the current conversation; a 60-box diagram convinces nobody.

Frequently asked questions

Do I still need ER diagrams if I use an ORM?

Yes — arguably more. An ORM gives you models in code, but the relationships between them stay scattered across a dozen files. The diagram is the one place where the whole structure is on a single page. If your ORM migrations are the source of truth, generate the diagram from them; the point is not to draw by hand, it is to see the whole.

Is an ER diagram the same as a database schema?

No. The schema is the executable definition — DDL, constraints, triggers. The diagram is a view of it, omitting everything that does not affect structure (indexes, storage options, permissions). A schema without a diagram is hard to take in; a diagram without a schema is only a sketch.

What is the difference between a logical and a physical ER diagram?

A logical diagram shows entities, attributes and relationships without vendor specifics — no SERIAL, no VARCHAR sizes. A physical diagram shows actual tables, columns and types as they will exist in PostgreSQL or MySQL. Early design lives at the logical level; documentation of a real system is physical.