What is an ER diagram? Definition, symbols, and examples

An ER diagram (entity relationship diagram, or ERD) is a picture of how a database is structured: the things it stores, the details it keeps about each one, and how those things relate. In a relational database that means tables drawn as boxes, their columns listed inside, and lines between tables for the foreign keys that connect them.

It is the most common kind of database diagram, and the fastest way to understand a schema you did not design or to agree on one you are about to build.

The three building blocks

• Entities are the things the database stores: customers, orders, products. In a physical ER diagram each entity is a table, drawn as a box with its name at the top.
• Attributes are the details kept about each entity: a customer’s email, an order’s status. They are the columns, usually listed with their data types, with the primary key (the column that identifies a row) marked.
• Relationships say how entities connect: a customer places orders, an order contains products. In the database a relationship is a foreign key, a column in one table that points at the primary key of another, and in the diagram it is a line between them.

Cardinality: reading the ends of the lines

A line on its own says two tables are related. The symbols at each end say how many rows can take part, which is called cardinality. Most ER diagrams today use crow’s foot notation, named for the three-pronged symbol that means "many":

• One-to-one: a user has one profile, and a profile belongs to one user. A single bar at each end.
• One-to-many: a customer has many orders, and each order has one customer. A bar at the customer end, a crow’s foot at the orders end. This is by far the most common relationship.
• Many-to-many: an order contains many products, and a product appears in many orders. A relational database cannot store this directly, so it becomes a join table, order_items, with a one-to-many line to each side.

Full crow’s foot notation also marks whether a side is optional (a circle) or required (a bar), so "zero or many" and "one or many" look different. Many tools, DB Planner included, draw the one-or-many part on the line and show whether the foreign key column allows nulls on the table itself.

An example

Here is a small store schema written in DBML, a readable text format that ER diagram tools draw from:

Table customers {
  id uuid [pk]
  email varchar [unique, not null]
}

Table orders {
  id uuid [pk]
  customer_id uuid [not null, ref: > customers.id]
  placed_at timestamp [not null]
}

Table products {
  id uuid [pk]
  name varchar [not null]
  price_cents int [not null]
}

Table order_items {
  order_id uuid [ref: > orders.id]
  product_id uuid [ref: > products.id]
  quantity int [not null]
}

Drawn as an ER diagram, that is four boxes. customers connects to orders with a one-to-many line, and order_items sits between orders and products, turning their many-to-many relationship into two one-to-many lines. Reading the picture, you can answer "can an order have no customer?" (no, the key is not null) without opening any code. Larger worked examples, from e-commerce to hospital management, are in the database schema templates, each with its ER diagram.

Conceptual, logical, and physical ER diagrams

ER diagrams are drawn at three levels of detail, depending on who they are for:

• Conceptual: entities and relationships only, no columns. Used early, with people who do not need the details.
• Logical: adds attributes and keys, still independent of any particular database.
• Physical: the real tables, column types, and constraints for a specific database such as PostgreSQL or MySQL. This is what developers mean by an ER diagram most of the time, and what tools generate from SQL.

Chen notation vs crow’s foot

The original ER notation, from Peter Chen’s 1976 paper, draws entities as rectangles, attributes as ovals, and relationships as diamonds of their own. It is still taught in database courses and works well for conceptual modeling. For physical schemas it takes a lot of room, which is why crow’s foot notation, with attributes listed inside the entity box and relationships as plain lines, is the default in most database tools. UML class diagrams are a third style you will see, closer to crow’s foot than to Chen.

How to make an ER diagram

There are three practical routes, depending on what you start from:

• From an existing database: export the schema as SQL and import it, and the diagram is generated for you. See SQL to ER diagram.
• From scratch: describe the tables in DBML and let the ER diagram tool draw them, which keeps the diagram and the schema from drifting apart.
• By hand: drag boxes and lines in a general diagramming tool. Fine for a sketch; the risk is that it stops matching the real database the first time the schema changes and nobody redraws it.

Whichever route you take, the diagram earns its keep when the team can look at the same one and it is still true. That is the case for drawing it from the schema rather than next to it.