Skip to content

ER Diagrams

Visualize database design with entity-relationship diagrams and notation.

What you'll learn

  • What an ER diagram is and why it is useful
  • Entities, attributes, and relationships in ER notation
  • Crow's foot notation for cardinality
  • Reading and drawing ER diagrams
  • Translating an ER diagram into SQL tables

Concept

What Is an ER Diagram?

An Entity-Relationship (ER) diagram is a visual representation of a database design. It shows entities (tables), their attributes (columns), and the relationships between them. ER diagrams are the standard way to communicate a database design before writing any SQL. They help you spot missing relationships, redundant data, and normalization issues early — when changes are cheap.

Entities and Attributes

An entity is drawn as a rectangle and represents a table. Attributes are drawn as ovals connected to the entity (in Chen notation) or listed inside the rectangle (in crow's foot notation, which is more common in practice). The primary key attribute is underlined. Attributes can be marked as required (NOT NULL) or optional (nullable).

Relationships and Cardinality

A relationship is drawn as a diamond (Chen) or a line between entities (crow's foot). The cardinality — how many instances of one entity relate to another — is shown with symbols:

  • One — a single line
  • Many — a "crow's foot" (three-pronged fork)
  • Exactly one — a line with a bar
  • Zero or one — a circle and a bar
  • Zero or many — a circle and a crow's foot

For example, a one-to-many relationship from User to Order is drawn with a single line at the User end and a crow's foot at the Order end.

Reading an ER Diagram

Start at one entity, follow the relationship line, and read the cardinality at each end. "A user has many orders" — the crow's foot at the Order end means many. "An order belongs to exactly one user" — the bar at the User end means exactly one. Many-to-many relationships in an ER diagram become junction tables in SQL.

Translating to SQL

Each entity becomes a CREATE TABLE. Each attribute becomes a column with its data type. One-to-many relationships become a foreign key on the "many" side. Many-to-many relationships become a junction table with two foreign keys. The ER diagram is the blueprint; the SQL is the implementation.

Example

              -- ER Diagram (text representation):
--
-- [USER] --1:N-- [ORDER] --M:N-- [PRODUCT]
--   |            |
--   |            +--1:N-- [ORDER_ITEM]
--   |
--   +--M:N-- [GROUP]
--
-- Translating the ER diagram to SQL:

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

CREATE TABLE orders (
  id         BIGSERIAL PRIMARY KEY,
  user_id    BIGINT NOT NULL REFERENCES users(id),  -- 1:N from users
  total      DECIMAL(10, 2) NOT NULL DEFAULT 0
);

CREATE TABLE products (
  id    BIGSERIAL PRIMARY KEY,
  name  VARCHAR(200) NOT NULL,
  price DECIMAL(10, 2) NOT NULL
);

-- M:N between orders and products -> junction table
CREATE TABLE order_items (
  order_id   BIGINT NOT NULL REFERENCES orders(id),
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity   INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

-- M:N between users and groups -> junction table
CREATE TABLE groups (id BIGSERIAL PRIMARY KEY, name VARCHAR(100) NOT NULL);
CREATE TABLE user_groups (
  user_id  BIGINT NOT NULL REFERENCES users(id),
  group_id BIGINT NOT NULL REFERENCES groups(id),
  PRIMARY KEY (user_id, group_id)
);
            

A text ER diagram showing users, orders, products, and groups with 1:N and M:N relationships, then the complete SQL translation with junction tables.

Try it

Common mistakes

The mistake

Drawing many-to-many relationships without a junction entity

The fix

Every M:N relationship needs a junction entity (association table) in the ER diagram. It is not just a line — it is an entity with its own attributes.

The mistake

Not marking cardinality clearly on relationship lines

The fix

Always annotate both ends of a relationship line with the cardinality (1, N, 0..1, 0..N). Ambiguous lines lead to incorrect SQL.

Look up unfamiliar terms

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

View glossary

Build real projects

Apply what you learned by building guided projects with starter code and solutions.

View projects

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors