Skip to content
M2TOOLKIT
Developer Resources

How to Visualize Database Relationships With an ER Diagram

Draw clear entity-relationship diagrams: primary and foreign keys, one-to-one, one-to-many and many-to-many relationships, crow's-foot notation and a worked shop example.

By Published 5 min read

Woman with a tablet standing in a server room
Photo: Christina Morillo on StockSnap (CC0 1.0)

Quick answer

Draw an entity-relationship diagram (ERD): one box per table listing its columns, with the primary key marked, and lines between tables for each foreign key. Mark each line's cardinality — one-to-one, one-to-many or many-to-many — usually with crow's-foot notation, and resolve many-to-many relationships with a junction table.

Once a database has more than a handful of tables, it's hard to keep the structure in your head. Which table owns which data? What happens to orders when a customer is deleted? An ERD answers those questions at a glance, which makes it invaluable when designing a schema, reviewing changes or onboarding someone new.

In this article
  1. The building blocks
  2. The three kinds of relationship
  3. Reading crow's-foot notation
  4. Worked example: a small online shop
  5. How to draw a clear ERD
  6. Related diagrams

The building blocks

TermMeaningExample
EntityA thing you store data about — usually a tablecustomers, orders, products
AttributeA property of the entity — a columnemail, created_at, price
Primary key (PK)The column(s) that uniquely identify a rowcustomers.id
Foreign key (FK)A column that refers to another table's primary keyorders.customer_id → customers.id
RelationshipHow rows in two tables connectA customer places orders
CardinalityHow many rows can be on each sideOne customer, many orders

The three kinds of relationship

One-to-many (1:N) — the most common

One customer can place many orders; each order belongs to one customer. The foreign key goes on the “many” side: orders.customer_id.

One-to-one (1:1)

Each user has at most one profile. Put a foreign key with a UNIQUE constraint on one side, e.g. profiles.user_id. Often used to split rarely used or sensitive columns into their own table.

Many-to-many (M:N)

An order contains many products, and a product appears in many orders. Relational databases model this with a junction table (also called a join or bridge table) holding two foreign keys:

CREATE TABLE order_items (
  order_id   INT NOT NULL REFERENCES orders(id),
  product_id INT NOT NULL REFERENCES products(id),
  quantity   INT NOT NULL DEFAULT 1,
  unit_price DECIMAL(10,2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

The junction table is a good place for data about the relationship itself — here, quantity and the price at the time of the order.

Reading crow's-foot notation

Crow's-foot notation puts symbols at each end of a relationship line. Each end shows a minimum and a maximum:

Symbol at the line endMeaning
Two bars ( || )Exactly one (one and only one)
Circle + bar ( o| )Zero or one (optional)
Bar + crow's foot ( |< )One or many
Circle + crow's foot ( o< )Zero or many

So customers ||——o< orders reads: “each order belongs to exactly one customer; a customer has zero or more orders.” Read each symbol from the other table's point of view: the symbol next to orders says how many orders one customer can have.

Worked example: a small online shop

customers  (id PK, name, email UNIQUE, created_at)
orders     (id PK, customer_id FK → customers.id, status, placed_at)
products   (id PK, name, price, sku UNIQUE)
order_items(order_id FK → orders.id, product_id FK → products.id,
            quantity, unit_price, PK(order_id, product_id))
  • customers ||——o< orders: a customer can exist with no orders yet.
  • orders ||——|< order_items: every order has at least one line.
  • products ||——o< order_items: a product may not have sold yet.

Drawn as an ERD, the orders → order_items ← products shape immediately shows the many-to-many relationship between orders and products.

Close-up of computer memory chips on a circuit board
Close-up of computer memory chips on a circuit boardPhoto: Charlie Belvin Designs on StockSnap (CC0 1.0)

How to draw a clear ERD

  1. 1
    List the entities — the nouns in your requirements (customer, order, product).
  2. 2
    Add key attributes and mark the primary key of each.
  3. 3
    Connect related entities and decide the cardinality at both ends.
  4. 4
    Resolve many-to-many relationships with junction tables.
  5. 5
    Add foreign keys on the “many” side and decide what happens on delete (restrict, cascade or set null).
  6. 6
    Arrange for readability: central tables in the middle, few crossing lines, related tables grouped.

For an existing database, you can work backwards: export the schema as SQL and import it into a diagramming tool instead of drawing by hand.

Try the free ERD MakerDraw tables and crow's-foot relationships, import SQL or DBML, and generate SQL for several databases.

Related diagrams

An ERD shows how data is structured. To show how data moves — from a form to an API to a database and out to a report — use a data-flow diagram. For application logic, a flowchart. Together they document a system far better than any one of them alone. See how to organize information visually for choosing between them.

Try the free Data Flow Diagram MakerMap processes, data stores and external entities.Try the free SQL FormatterTidy the CREATE TABLE statements behind your diagram.

Frequently asked questions

What is an entity-relationship diagram?

A diagram of a database's structure: each entity (usually a table) is a box listing its attributes, and lines between them show relationships and their cardinality.

How do you represent a many-to-many relationship?

With a junction table that holds foreign keys to both tables, such as order_items linking orders and products. Its primary key is often the pair of foreign keys.

Which side gets the foreign key in a one-to-many relationship?

The “many” side. In customers-to-orders, orders.customer_id refers to customers.id.

What does crow's-foot notation mean?

Symbols at each end of a relationship line show the minimum and maximum number of related rows: a bar means one, a circle means zero, and the three-pronged “crow's foot” means many.

M2Toolkit Editorial Team

The people who build M2Toolkit's tools write these guides. Every formula and example in an article is checked against the matching tool, and articles are reviewed when the tool changes.

All articles →

Free tools for this