ER Diagrams

Before we write a single line of SQL, we need a blueprint. That blueprint is the Entity-Relationship (ER) Diagram. It’s a visual representation of our data model — what entities exist, what attributes they have, and how they relate to each other.

Think of it like an architect’s floor plan. We don’t start building walls before we know where the rooms, doors, and hallways go.

The Three Building Blocks

1. Entities

An entity is a “thing” we want to store data about. Users, products, orders, departments — these are all entities. Each entity becomes a table in our database.

2. Attributes

Attributes are the properties of an entity. A User entity might have: id, name, email, created_at. Each attribute becomes a column in the table.

• Primary Key — uniquely identifies each row (usually id)
• Required vs Optional — NOT NULL vs nullable
• Derived — computed from other attributes (like age from birth_date)

3. Relationships

How entities connect to each other. A User places Orders. An Order contains Products. These connections are the relationships.

A Simple ER Model

Cardinality — How Many on Each Side?

Cardinality describes how many instances of one entity relate to another. There are three main types:

One-to-One (1:1)

Each row in Table A relates to exactly one row in Table B, and vice versa.

Example: Each user has exactly one profile. Each profile belongs to exactly one user.

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE user_profiles (
    id SERIAL PRIMARY KEY,
    user_id INT UNIQUE REFERENCES users(id),  -- UNIQUE makes it 1:1
    bio TEXT,
    avatar_url VARCHAR(500)
);

One-to-one relationships are rare. They’re usually used to split a wide table into two (for performance or security reasons).

One-to-Many (1:N)

One row in Table A can relate to many rows in Table B, but each row in Table B relates to only one row in Table A.

Example: One department has many employees. Each employee belongs to one department.

CREATE TABLE departments (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department_id INT REFERENCES departments(id)  -- FK on the "many" side
);

This is the most common relationship type. The foreign key always goes on the “many” side.

Many-to-Many (M:N)

Rows in Table A can relate to multiple rows in Table B, and vice versa.

Example: One order can contain many products. One product can appear in many orders.

We can’t directly represent this with a foreign key. We need a junction table (also called a bridge table or join table).

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    user_id INT REFERENCES users(id),
    total DECIMAL(10,2)
);

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

-- Junction table: connects orders and products
CREATE TABLE order_items (
    id SERIAL PRIMARY KEY,
    order_id INT REFERENCES orders(id),
    product_id INT REFERENCES products(id),
    quantity INT DEFAULT 1,
    unit_price DECIMAL(10,2)  -- price at time of purchase (denormalized)
);

Reading an ER Diagram

When we see an ER diagram, here’s how to translate it:

1. Each box becomes a table
2. Each attribute becomes a column
3. Lines between boxes become foreign keys
4. The “1” side doesn’t need anything special
5. The “N” side gets the foreign key column
6. “M:N” relationships need a junction table

Common ER Diagram Notations

There are several notation styles. The two most common are:

• Chen notation — uses diamonds for relationships, ovals for attributes (more academic)
• Crow’s foot notation — uses fork symbols to show cardinality (more practical, industry standard)

In crow’s foot notation:

• A single line means “one”
• A fork (crow’s foot) means “many”
• A circle means “optional” (zero)
• A dash means “mandatory” (one)

Practical Tips for Drawing ER Diagrams

1. Start with entities — identify the nouns (User, Product, Order, Payment)
2. Add relationships — identify the verbs (places, contains, pays for)
3. Determine cardinality — ask “can one X have many Y?” for both directions
4. Add attributes — what do we need to know about each entity?
5. Identify keys — every entity needs a primary key
6. Normalize — check for redundancy and split tables if needed

Tools for Drawing ER Diagrams

• dbdiagram.io — write schema in code, get a visual diagram (free, great for quick work)
• Lucidchart — drag and drop, good for presentations
• draw.io (diagrams.net) — free, versatile, works offline
• pgAdmin — can generate ER diagrams from existing PostgreSQL databases

Interview Tip

If an interviewer asks us to design a schema, always start with an ER diagram (even a rough one on the whiteboard). It shows structured thinking. Identify entities first, then relationships, then attributes. Don’t jump straight into writing CREATE TABLE statements.