Stop 03 · SQL for Data AnalysisTopics · 10/10

Database Design Basics (ER Diagrams)

In short

On day one at a new job, someone shares a database with 80 tables. An ER diagram is the map that tells you which table holds what and how to join them.

Understanding how tables relate through primary and foreign keys, visualized in an Entity-Relationship diagram — enough to navigate an unfamiliar schema quickly.

The jaadu connects blank cards with coloured strings on a big chart paper.

Think of it like this

The wedding family tree

At a big Indian wedding, you need a family chart: who is whose cousin, which uncle belongs to which side. Each person is a card, each relation a line. An ER diagram is that chart for tables, with keys showing who's related to whom.

Watch it happen

A small e-commerce schema

customers

orders

order_items

payments

products

PK customer_id

FK customer_id

1/5customers has a primary key, customer_id. Each value is unique and identifies one person.

The big ideas

What you need to know

Primary key

A column whose value is unique for every row, like order_id. It identifies exactly one row.

Foreign key

A column pointing to another table's primary key, like orders.customer_id. It's what you join on.

One-to-many

The most common relation: one customer, many orders. Expect rows to multiply when you join down.

Fact and dimension tables

Warehouses often have big event tables (orders) and smaller lookup tables (products, cities).

Save yourself the pain

Common mistakes

Oops

Joining tables on columns that just happen to share a name.

Do this instead

Check the ER diagram or ask which column is the real foreign key before joining.

Oops

Summing order totals after joining to order_items.

Do this instead

Order totals repeat once per item after that join. Aggregate at the right level first.

Your mission

Draw a schema

~30 min

Quick check

orders.customer_id points to customers.customer_id. What is orders.customer_id?

Go deeper

1Sketch an ER diagram for a simple e-commerce database (users, orders, products).
2Learn to identify primary keys and foreign keys in an unfamiliar schema.
3Practice reading a real company's (public or sample) schema documentation.