System Design HLD-06.01
Relational databases and SQL
Tables, rows and keys, normalization from 1NF to 3NF, joins, and constraints: why a relational database is the safe default for most products.
The big idea
A relational databaseA database that stores data in tables of rows and columns, links tables through keys, and is queried with SQL. stores data in tables, like spreadsheets with strict rules. Each table holds one kind of thing (customers, products, orders), each row is one of those things, and each column is one property. Tables point at each other through keys: an order row stores its customer's id rather than a copy of the customer. You then ask questions with SQL, a declarative language where you describe what you want, such as "total spend per customer", and the database works out how to compute it.
Two features make relational databases the safe default for most products. They keep each fact in one place, so updating it cannot leave contradictory copies behind. And they enforce rules for you, rejecting a duplicate email or an order for a customer that does not exist, so bad data never gets in. PostgreSQL, MySQL, SQL Server and SQLite are all relational.
Think of a well-run library catalog. Each book's details are recorded once on its card; loans refer to the book by its number rather than copying the title and author onto every loan slip.
How it works
Step 1: Tables and keys
- A primary keyA column, or set of columns, whose value uniquely identifies each row in a table. uniquely identifies each row, such as
customers.id. - A foreign keyA column that stores the primary key of a row in another table, linking the two, and that the database can check actually exists. stores another table's primary key to link rows:
orders.customer_idpoints tocustomers.id. order_itemslinks orders and products in a many-to-many relationship: one order has many products, and one product appears in many orders.
Step 2: Normalization
NormalizationOrganizing tables so each fact is stored in exactly one place, removing duplication that could lead to inconsistent data. means designing tables so each fact lives in exactly one place. Imagine one wide orders table storing the customer's email and the product's price on every order line. Change Ada's email and you must update every order she ever placed; miss one and the data contradicts itself. The normal forms are the rules for avoiding this:
- First normal form (1NF): one value per cell. No
products = "Keyboard, Mouse"lists in a single column; use rows. - Second normal form (2NF): every column depends on the whole key. In
order_itemskeyed by (order, product), the product's name depends only on the product, so it belongs inproducts. - Third normal form (3NF): no column depends on another non-key column. If
ordersstored bothcustomer_idandcustomer_email, the email depends on the customer, not the order, so it belongs incustomers.
In practice, "every fact in one place, referenced by key" gets you most of the way. Later, for speed, you may deliberately duplicate some data (denormalize), as covered in Denormalization, materialized views and CQRS .
Step 3: Querying with joins and constraints
A joinA query operation that combines rows from two or more tables based on matching key values. recombines the normalized tables at query time. Constraints guard the data on the way in:
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("PRAGMA foreign_keys = ON")
db.executescript("""
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
name TEXT NOT NULL
);
CREATE TABLE products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price NUMERIC NOT NULL CHECK (price >= 0)
);
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
created_at TEXT NOT NULL
);
CREATE TABLE order_items (
order_id INTEGER NOT NULL REFERENCES orders(id),
product_id INTEGER NOT NULL REFERENCES products(id),
qty INTEGER NOT NULL CHECK (qty > 0),
PRIMARY KEY (order_id, product_id)
);
INSERT INTO customers VALUES (1, 'ada@example.com', 'Ada'), (2, 'grace@example.com', 'Grace');
INSERT INTO products VALUES (10, 'Keyboard', 49.0), (11, 'Mouse', 19.0), (12, 'Monitor', 199.0);
INSERT INTO orders VALUES (100, 1, '2026-10-01'), (101, 2, '2026-10-02'), (102, 1, '2026-10-03');
INSERT INTO order_items VALUES (100, 10, 1), (100, 11, 2), (101, 12, 1), (102, 11, 1);
""")
print("Spend per customer (a join across four tables):")
for row in db.execute("""
SELECT c.name, COUNT(DISTINCT o.id) AS orders, SUM(oi.qty * p.price) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
GROUP BY c.name ORDER BY spent DESC"""):
print(" ", row)
for label, sql in [
("duplicate email ", "INSERT INTO customers VALUES (3, 'ada@example.com', 'Imposter')"),
("unknown customer", "INSERT INTO orders VALUES (103, 99, '2026-10-03')"),
("negative price ", "INSERT INTO products VALUES (13, 'Glitch', -5)"),
]:
try:
db.execute(sql)
print(label, "-> accepted")
except sqlite3.IntegrityError as e:
print(label, "-> rejected:", e)Output
Spend per customer (a join across four tables):
('Grace', 1, 199)
('Ada', 2, 106)
duplicate email -> rejected: UNIQUE constraint failed: customers.email
unknown customer -> rejected: FOREIGN KEY constraint failed
negative price -> rejected: CHECK constraint failed: price >= 0 One query joins four tables to compute each customer's spend. Then the database itself refuses three kinds of bad data: a duplicate email (UNIQUE), an order for a customer who does not exist (FOREIGN KEY) and a negative price (CHECK). Rules enforced by the database hold no matter which application, script or engineer writes the data. Copy the code and change the queries to experiment; it needs only Python.
Going deeper
Why "safe default"
Relational databases give you flexible queries (you do not need to know every future question when you design the schema), strong consistency with transactions ( Transactions and ACID ), decades of tooling, and enough performance for the vast majority of products: a single well-tuned server handles thousands of transactions per second and many terabytes. The cases where something else wins, enormous write volumes, very simple key lookups at huge scale, graph traversals, full-text search, are covered in NoSQL families and Choosing the right database .
Schema changes
Changing a table that holds a billion rows can lock it for a long time. Large teams use online schema change tools and the expand-and-contract pattern: add the new column, backfill it, switch the code, then drop the old column, each step deployable on its own.
Trade-offs
| Option | Wins when | Loses when |
|---|---|---|
| Normalized schema | Correctness: each fact once, simple updates, flexible queries | Read-heavy hot paths that need many joins on every request |
| Denormalized copies | Very frequent reads that would otherwise join many tables | Write complexity and the risk of copies disagreeing |
| Constraints in the database | Data integrity regardless of which code writes | Some write overhead, and flexibility during messy migrations |
Common mistakes
- Storing lists or comma-separated values in a single column.
- Copying the same fact into many tables and updating only some of them.
- Skipping foreign keys and unique constraints, then cleaning up orphaned and duplicate rows forever.
- Choosing a NoSQL store for a product whose data is naturally relational, then rebuilding joins in application code.
Check yourself
-
What does a foreign key do?
Show answer
B. Links a row to a row in another table by storing its primary key, and lets the database check it exists orders.customer_id references customers.id, and the database rejects ids that do not exist.
-
Ada's email is stored on every one of her 200 orders. What is the risk?
Show answer
B. Updating her email requires changing 200 rows; missing one leaves contradictory data Duplicated facts drift apart. Normalization stores the email once, in customers.
-
Which violates first normal form?
Show answer
B. An orders table with a column holding 'Keyboard, Mouse, Monitor' 1NF requires one value per cell; a list belongs in separate rows.
-
Why enforce rules like UNIQUE in the database rather than only in application code?
Show answer
B. The database enforces them for every writer, including scripts and other services Concurrent writers and other code paths can bypass app checks; constraints hold for all.
Recap
- Relational databases store data in tables linked by primary and foreign keys.
- Normalization keeps each fact in one place so updates stay consistent.
- SQL joins recombine tables at query time; you describe what you want, not how.
- Constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK) keep bad data out for every writer.
- For most new products, a relational database is the right default.
Next up
- I know nothing yet learning path