Skip to content
learn
  1. Learn
  2. High Level System Design
  3. Databases and Storage Fundamentals

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.

Beginner 15 min Last reviewed

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

An entity-relationship diagram for a small shop Four tables. Customers has id, email and name. Orders has id, customer_id and created_at, with customer_id pointing to customers.id. Order_items has order_id, product_id and qty, pointing to orders.id and products.id. Products has id, name and price. customer_id order_id product_id customers id (PK) . email (unique) . name orders id (PK) . customer_id (FK) . created_at order_items order_id (FK) . product_id (FK) . qty products id (PK) . name . price Figure 1. Each table stores one kind of thing. Glowing links are foreign keys: an order points to its customer, and each order item points to its order and its product. No fact is stored twice.
  • 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_id points to customers.id.
  • order_items links 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_items keyed by (order, product), the product's name depends only on the product, so it belongs in products.
  • Third normal form (3NF): no column depends on another non-key column. If orders stored both customer_id and customer_email, the email depends on the customer, not the order, so it belongs in customers.

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:

sql.pyruns with python3 (standard library sqlite3)
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

OptionWins whenLoses 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

  1. 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.

  2. 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.

  3. 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.

  4. 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.

Get new lessons by email

Get new posts on backend and AI internals. No spam, unsubscribe anytime.

Your email is only used to send new posts. Privacy

Next up