Back to all posts

Entity Relationship Diagram (ERD): Symbols, Examples, and How to Create One

For anyone who has to read or draw a database diagram; keys, cardinality and the notations are explained where they appear.

On this page

You open a database with sixty tables and no documentation, and you need to see how the tables connect. An entity relationship diagram (ERD) shows that on one page. Each table is a box with its columns inside, and each relationship is a line between two boxes. The marks at the ends of a line say how many rows can sit on each side.

DbSchema draws this diagram for you when you connect to an existing database.

Try DbSchema on your own database

Free Community Edition plus a 15-day Architect trial. No signup. Windows, macOS and Linux.

What an entity relationship diagram is

An ER diagram is built from five things:

  • An entity is a thing that the database stores, drawn as a box. It becomes a table.
  • An attribute is one fact about an entity, such as a name or a title. It becomes a column.
  • The primary key is the attribute, or set of attributes, that identifies one row.
  • A foreign key is a column that stores the key of a row in another table, such as the publisher of a book. The table that holds it is the child, and the table it points to is the parent.
  • A relationship is the line that a foreign key draws between two boxes.

Peter Chen described the model in 1976. His paper, "The Entity-Relationship Model: Toward a Unified View of Data", appeared in ACM Transactions on Database Systems. His notation draws entities, attributes and relationships as separate shapes. Database tools such as DbSchema draw crow's foot notation instead.

If keys still feel loose, read Primary Key in SQL and What Is a Foreign Key?.

Entity relationship diagram example

Create this small library database to follow along. The statements run as written on PostgreSQL, MySQL and SQLite:

CREATE TABLE publishers (
  publisher_id INT PRIMARY KEY,
  name         VARCHAR(100) NOT NULL
);

CREATE TABLE books (
  book_id      INT PRIMARY KEY,
  title        VARCHAR(200) NOT NULL,
  publisher_id INT,
  sequel_of    INT,
  FOREIGN KEY (publisher_id) REFERENCES publishers (publisher_id),
  FOREIGN KEY (sequel_of)    REFERENCES books (book_id)
);

CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  name      VARCHAR(100) NOT NULL
);

CREATE TABLE book_authors (
  book_id   INT NOT NULL,
  author_id INT NOT NULL,
  PRIMARY KEY (book_id, author_id),
  FOREIGN KEY (book_id)   REFERENCES books (book_id),
  FOREIGN KEY (author_id) REFERENCES authors (author_id)
);

INSERT INTO publishers VALUES (1, 'Fernhill Press');
INSERT INTO books VALUES (10, 'Field Guide to Ferns', 1, NULL), (11, 'More Ferns', NULL, 10);
INSERT INTO authors VALUES (1, 'Maria Vega'), (2, 'Tom Ellis');
INSERT INTO book_authors VALUES (10, 1), (10, 2), (11, 1);

Each table becomes a box on the diagram, and each FOREIGN KEY clause becomes a line:

DbSchema diagram of the library database: publishers, books, authors and book_authors, with foreign key lines and a line from books back to itself

The books table has three lines: one to publishers, one to book_authors, and one that loops back to books through sequel_of.

To list each book with its authors, follow the two lines through book_authors:

SELECT b.title, a.name
FROM books b
JOIN book_authors ba ON ba.book_id = b.book_id
JOIN authors a ON a.author_id = ba.author_id
ORDER BY b.title, a.name;
titlename
Field Guide to FernsMaria Vega
Field Guide to FernsTom Ellis
More FernsMaria Vega

Field Guide to Ferns has two authors, so it comes back twice. More Ferns has one.

How to read an ER diagram

Read a diagram in three passes: the keys first, then the lines, then the marks at the line ends.

Look atWhat it tells you
primary key columnshow each row is identified
foreign key columnswhich table depends on which
a table made of two foreign keysa many-to-many relationship
the marks at each line endone or many, and whether a row is required

Start at the table that most lines touch, such as books in the library, and work outward.

Cardinality in an ER diagram

Cardinality says how many rows on one side of a line can match one row on the other side. The diagram draws cardinality as marks at the line ends. The database sets it with primary keys, NOT NULL and UNIQUE.

Each line end says two things. The maximum is one or many. The minimum is zero or one, and a minimum of zero makes the relationship optional.

One-to-many (1:N)

One-to-many means that one parent row can match many child rows. For example, one publisher can publish many books, and each book has at most one publisher.

The foreign key sits on the many side, here books.publisher_id. That column allows NULL, so a book can have no publisher: More Ferns has none.

Many-to-many (M:N)

Many-to-many means that a row on either side can match many rows on the other. For example, a book can have several authors, and an author can write several books. No relational database stores a many-to-many relationship directly. A junction table takes its place, with one foreign key to each side: book_authors in the library.

Try to add Maria Vega (author 1) to Field Guide to Ferns (book 10) a second time. The primary key of book_authors covers both columns, so each pair can be stored only once:

INSERT INTO book_authors VALUES (10, 1);

PostgreSQL 18 refuses the row:

ERROR:  duplicate key value violates unique constraint "book_authors_pkey"
DETAIL:  Key (book_id, author_id)=(10, 1) already exists.

One-to-one (1:1)

One-to-one means that a row matches at most one row on the other side. For example, a book can have one cover image. Add a book_covers table for it:

CREATE TABLE book_covers (
  book_id   INT PRIMARY KEY,
  image_url VARCHAR(200) NOT NULL,
  FOREIGN KEY (book_id) REFERENCES books (book_id)
);

INSERT INTO book_covers VALUES (10, 'ferns.jpg');
INSERT INTO book_covers VALUES (10, 'ferns-2.jpg');

The second insert fails, because book_id is the foreign key and also the primary key. PostgreSQL 18 reports:

ERROR:  duplicate key value violates unique constraint "book_covers_pkey"
DETAIL:  Key (book_id)=(10) already exists.

A foreign key that is also unique makes a line one-to-one. Use a one-to-one table to keep large or rarely read columns, such as an image, apart from the main row.

Recursive relationships

A recursive relationship is a foreign key that points at its own table. For example, books.sequel_of holds the book_id of the book that a sequel follows. The diagram draws it as a line from books back to books. A self-join reads it:

SELECT s.title AS sequel, b.title AS follows
FROM books s
JOIN books b ON b.book_id = s.sequel_of;
sequelfollows
More FernsField Guide to Ferns

Field Guide to Ferns has no sequel_of value, so it isn't a sequel and gets no row of its own.

Common ERD symbols and notations

Crow's foot notation

Crow's foot notation, also called Information Engineering, draws each table as a box with its columns inside. Each line end carries two marks. The mark next to the box is the maximum, and the mark further out is the minimum:

The four crow's foot line ends at a table: two bars for exactly one, a circle and a bar for zero or one, a bar and a crow's foot for one or many, a circle and a crow's foot for zero or many

The marks follow from the foreign key column of the child table. The mark at the parent end says how many parent rows one child row can have, and the mark at the child end the reverse:

Foreign key columnMark at the parent endMark at the child end
nullablezero or onezero or many
NOT NULLexactly onezero or many
nullable, UNIQUEzero or onezero or one
NOT NULL, UNIQUEexactly onezero or one

In the library, books.publisher_id is nullable, so the publishers end shows zero or one. In book_covers, book_id is the primary key, which makes it NOT NULL and unique. So the books end shows exactly one, and the book_covers end shows zero or one.

A foreign key can't require a child row, so the table has no one or many. A diagram may still show one or many at the child end, for a rule that the application enforces.

Chen notation

Chen notation draws every part of the model as its own shape:

ShapeMeaning
rectangleentity
double rectangleweak entity
diamondrelationship
double diamondthe relationship that identifies a weak entity
ellipseattribute
underlined name in an ellipsekey attribute
double ellipsemultivalued attribute
dashed ellipsederived attribute

A weak entity has no key of its own, and its rows are identified through the entity it depends on. A chapter numbered within its book is one. A multivalued attribute, such as the several genres of a book, becomes a table of its own in a relational database. A derived attribute, such as the number of books an author wrote, is calculated rather than stored.

Here are authors and books in Chen notation. The underlined attributes are keys, and M and N on the lines mark the relationship as many-to-many:

Chen notation: the entities author and book as rectangles joined through the diamond writes, marked M and N, with the attributes author_id, name, book_id and title as ellipses

Chen draws a many-to-many relationship as one diamond. In tables, that diamond becomes the junction table book_authors.

Conceptual, logical, and physical ER diagrams

The same domain is drawn at three levels of detail over the life of a project.

LevelWhat it holdsUsed for
conceptualentities and relationships, few attributestalks with people who don't write SQL
logicalattributes, keys, cardinalitysettling the structure
physicaltables, column types, indexes, constraintscreating or documenting the database

The library schema above is physical: its CREATE TABLE statements name INT and VARCHAR. Logical Database Design: Keys and Normalization works through the logical level on one example.

How to create an ER diagram step by step

  1. List the entities, the things that the system stores, such as books and authors.
  2. List the attributes of each entity.
  3. Choose a primary key for each entity. A surrogate key, a number with no meaning of its own such as book_id, stays valid when a title changes.
  4. Draw a line wherever one entity refers to another, and mark both ends.
  5. Replace each many-to-many line with a junction table.
  6. Move repeating values into a table of their own, so that no column holds a list.
  7. Trace the application's most frequent queries along the lines.
  8. Publish the diagram where the team will look for it.

Step 7 finds what the others miss, such as a five-table join behind the screen that users open every morning.

If the database already exists, reverse-engineer it instead of drawing it by hand (Create ER diagrams in DbSchema). Then add comments that say what each column means.

ER diagram vs relational schema

An ER diagram and a relational schema describe the same tables in two different forms.

ER diagramRelational schema
a picture of entities and relationshipsthe definition of tables, columns, keys and constraints
read in design reviews and documentationrun to create the database
understood by people who don't write SQLexact enough for the database engine

The line from book_authors to books says that one table references the other. The line doesn't say what happens to the book_authors rows when a book is deleted. The schema says it with an ON DELETE action, such as CASCADE.

A UML class diagram is a third kind of picture. It describes objects in application code, with methods and inheritance, which have no equivalent in a table.

Common ERD mistakes

A diagram misleads its reader when it has any of these faults:

  • a table with no primary key
  • a line with no cardinality marks
  • a diagram that no longer matches the database

Without a primary key, nothing says which row a foreign key points at. Without cardinality marks, nothing says whether an order holds several items or an item belongs to several orders.

A diagram that no longer matches the database misleads more than no diagram, because the reader trusts it. Regenerate the diagram from the database rather than redrawing it.

Create ER diagrams in DbSchema

DbSchema builds the diagram from a live database, from your own drawing, or from a SQL script.

From a database, connect through its JDBC driver, such as the PostgreSQL JDBC driver or the MySQL JDBC driver, and tick the schemas to read. DbSchema reads the tables, columns and foreign keys, and draws them in a new model. Connecting and reading change nothing in the database.

To draw, right-click the diagram and choose Create Table. To create a foreign key, drag the child column onto the parent column, and the New Foreign Key dialog opens. When you're connected, DbSchema runs each change in the database at once. Offline, the changes stay in the model until you synchronize it with the database.

From a script, File → Import Model from SQL or Data Files reads CREATE TABLE statements, such as the library script above, and draws them without a database. Importing a model needs DbSchema Pro.

DbSchema reads the cardinality marks from NOT NULL and unique keys, as in the table under Crow's foot notation. Here are three foreign keys to an orders table:

DbSchema diagram in Information Engineering notation: a solid line from order_items with a bar and crow's foot, a dashed line from payments with a circle and crow's foot, and a solid line from shipments with a single bar at each end

A dashed line has a nullable child column. By default, DbSchema draws a NOT NULL column that isn't unique as one or many at the child end. To draw it as zero or many, as in the table above, tick View → Foreign Keys → Foreign Key Notation → Use Strict Conventions. The same menu switches the notation from the default, Information Engineering With Arrows, to Information Engineering, Barker or Idef1x.

Double-click a line to open the Edit Foreign Key dialog. There, On Delete and On Update set what the database does with the child rows. If the database has no foreign keys, drag the columns as above and tick Virtual in the dialog. A virtual foreign key draws a line that exists only in the DbSchema model.

Diagram → New Diagram adds a diagram for one part of a large schema. File → Export As... → HTML5/PDF Documentation writes the diagram and a description of every table and column as HTML5, PDF or Markdown.

Saving the model to a file, importing a script and synchronization need DbSchema Pro. For MySQL and PostgreSQL walkthroughs, read Create ER Diagrams for MySQL and for PostgreSQL.

Open a database you already run and let DbSchema draw it. Then look for tables with no lines at all: no foreign key reaches them. Download DbSchema and connect: reverse-engineering and the interactive diagram are in the free Community Edition. HTML5 documentation is in DbSchema Pro, and Community documents a diagram of up to 12 tables.

Draw the ER diagram from your own database

DbSchema reads the catalog over a JDBC connection and lays the tables and foreign keys out as an interactive diagram. Community Edition is free, with no table limit.