ER Diagrams in SQL: How Databases Are Designed

Your manager drops a database on your desk and says: "We need a report showing which products are selling and which customers are buying them." You open the database and find six tables. Without an ER diagram, you'd have to guess which columns connect orders to customers, or whether order_items links to products at all. An ER diagram answers those questions before you write a single line of SQL.

What an ER Diagram Actually Shows

An ER diagram is a picture of a database schema. Each box in the diagram represents a table. Inside each box are the column names. The lines between boxes show how the tables relate to each other.

Every table in a well-designed database has a primary key: a column whose value is unique for every row. In the customers table, that column is customer_id. No two customers share the same customer_id. That uniqueness is what makes the column useful as an identifier.

When another table needs to refer to a customer, it stores that customer_id as a foreign key. The orders table has a customer_id column. That column doesn't describe the order itself; it points back to the customers table. The line between customers and orders in an ER diagram represents exactly this relationship.

In the ecommerce schema, the relationships look like this:

The orders table sits in the middle. It connects to customers (who placed the order), employees (who handled it), order_items (what was in it), and payments (how it was paid for). Understanding that position tells you which table to start from when you're writing a query that spans multiple tables.

Types of Relationships

ER diagrams use specific notation to show how many rows on each side of a relationship can exist. The three types you'll see most often are:

One-to-many. One row in the first table connects to many rows in the second. A single customer can place many orders, but each order belongs to exactly one customer. This is the most common relationship type in relational databases.

One-to-one. One row connects to exactly one other row. Less common in practice. You might store sensitive employee data in a separate table and link each employee to exactly one record there.

Many-to-many. Many rows on both sides can connect. A product can appear in many orders, and an order can contain many products. Databases handle this with a junction table. In the ecommerce schema, order_items is that junction table. It sits between orders and products and holds one row for each product in each order.

Reading the ecommerce.db Schema as an ER Diagram

Here's how the ecommerce schema maps out as relationships:

• customers one-to-many orders: one customer, many orders
• employees one-to-many orders: one employee processes many orders
• orders one-to-many order_items: one order contains many line items
• products one-to-many order_items: one product appears in many order items
• orders one-to-many payments: one order can have more than one payment record

When you want to know which customer placed which order, you join customers and orders on customer_id. That join works because orders.customer_id is a foreign key pointing to customers.customer_id.

SELECT c.first_name, c.last_name, o.order_id, o.total_amount
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id

The ON clause in a JOIN is almost always a primary key matched to a foreign key. The ER diagram tells you exactly which columns to use.

When you need to trace a purchase all the way from customer to product, you follow the chain of relationships:

SELECT c.first_name, p.product_name, oi.quantity
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id

Each JOIN follows a line in the ER diagram. You're not guessing. You're reading the map.

How ER Diagrams Connect to Other Concepts

Once you can read an ER diagram, [JOINs](sqlround.com/lessons/sql-joins) become much more predictable. You already know which column connects which tables before you write anything.

[Primary and foreign keys](sqlround.com/lessons/sql-primary-foreign-keys) are the mechanism that makes the relationships in an ER diagram work at the database level. An ER diagram is the visual layer; keys are the technical implementation underneath.

Common Mistakes

1. Joining on the wrong column

Joining two tables on a column that looks similar but means something different gives you wrong results without an error message.

-- wrong
SELECT o.order_id, oi.quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.item_id

Check the ER diagram. orders and order_items connect on order_id, not item_id.

-- correct
SELECT o.order_id, oi.quantity
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id

2. Skipping the junction table

Trying to join orders directly to products fails because there is no direct foreign key between them. order_items is the bridge.

-- wrong
SELECT o.order_id, p.product_name
FROM orders o
JOIN products p ON o.order_id = p.product_id

Always go through the junction table when the ER diagram shows no direct line between two tables.

-- correct
SELECT o.order_id, p.product_name
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id

3. Assuming any shared column name is a join key

Both orders and payments have an amount column, but that doesn't mean you join on it. You join on order_id, which is the actual foreign key.

-- wrong
SELECT o.order_id, pay.method
FROM orders o
JOIN payments pay ON o.total_amount = pay.amount

The ER diagram shows the correct key clearly.

-- correct
SELECT o.order_id, pay.method
FROM orders o
JOIN payments pay ON o.order_id = pay.order_id

Quick Recap

• An ER diagram maps every table, column, and relationship in a database visually.
• A primary key uniquely identifies each row in its table.
• A foreign key in one table points to the primary key of another table, creating a relationship.
• One-to-many is the most common relationship type: one parent row, many child rows.
• Junction tables like order_items resolve many-to-many relationships between two other tables.
• Reading an ER diagram before writing a query tells you exactly which columns to join on and in what order.

SELECT e.employee_id, e.first_name, e.last_name, e.department
FROM employees e

SELECT pay.payment_id, pay.order_id, pay.amount, pay.method
FROM payments pay

SELECT e.first_name, e.last_name, o.order_id
FROM employees e
JOIN orders o ON e.employee_id = o.employee_id

SELECT oi.order_id, oi.product_id, oi.quantity
FROM order_items oi
WHERE oi.quantity > 2
ORDER BY oi.quantity DESC

SELECT e.department, COUNT(*) AS employee_count
FROM employees e
GROUP BY e.department