SQL: Database Design & Normalization

Two ways into the same idea

Required Fundamentals

Three things should be solid before you design:

Core Concepts

Good design makes mistakes hard to make: each fact is stored in exactly one place and the rules are written as constraints, so nobody can enter contradictory data even by trying. Normalization is the systematic way to get there.

1Entity, attribute, relationship

2Keys

3Why normalize? Anomalies

One giant table that stores everything (each line carrying the customer, the drink and the barista) repeats facts, and repetition breeds three problems:

4Functional dependency

We say A → B (A determines B) when the value of A fixes the value of B without ambiguity: drink_id → drink_name, price. This idea is the key to all of normalization: ask of every column: what determines it? If something that is not the table’s whole key determines it, the column is in the wrong place.

5The normal forms

6Small design decisions that matter

How Do We Design, Step by Step?

1. Describe the domain in sentences: “a customer places orders, an order contains drinks in quantities, a barista prepares the order”.
2. Extract entities and attributes: nouns become tables, descriptors become columns.
3. Pick a key for each entity (often a surrogate id) and identify the candidate keys you protect with UNIQUE.
4. Define relationships and counts, put the foreign key on the “many” side, and a junction table for many-to-many.
5. Normalize: write the functional dependencies and check 1NF, then 2NF, then 3NF.
6. Add constraints: NOT NULL, CHECK, DEFAULT, and decide an ON DELETE action for each foreign key.
7. Test with data: load a small sample and ask real questions; if a natural query turns out awkward, revisit the design.

Diagram 26, the normalization path: each step removes one specific kind of redundancy.

Diagram 27, our café’s final schema after normalization: five tables, one home for every fact.

Diagram 28, choosing an ON DELETE action is a question about the relationship’s meaning, not about syntax.

Worked Example: From One Sales Log to a Sound Schema

Imagine someone logged the café’s sales in a single table where every line carries everything:

Step 1, the starting (unnormalized) table

CREATE TABLE sales_log (
    order_id            INTEGER,
    drink_id            INTEGER,
    drink_name          TEXT,
    drink_category      TEXT,
    drink_price_halalas INTEGER,
    customer_phone      TEXT,
    customer_name       TEXT,
    customer_city       TEXT,
    barista_id          INTEGER,
    barista_name        TEXT,
    qty                 INTEGER,
    order_date          TEXT,
    PRIMARY KEY (order_id, drink_id)
);

(Only seven of the twelve columns are shown for brevity; the table also holds the customer’s phone, the barista id and the drink’s price.)

Is it in 1NF? Yes: every cell is one value and the key (order_id, drink_id) identifies each row. An example of what is not in 1NF:

Step 2, find the functional dependencies

Step 3, watch the anomalies happen

Update: Noura moved to Jeddah and we edited only one row, forgetting the rest:

UPDATE sales_log SET customer_city = 'Jeddah'
WHERE order_id = 101 AND drink_id = 1;   -- 1 row changed

SELECT DISTINCT customer_phone, customer_name, customer_city
FROM sales_log ORDER BY 1, 3;

Noura is now in two cities! A correct edit needed WHERE customer_phone = … to hit three rows. Deletion: deleting order 103 made “Spanish Latte” and Sultan vanish from the database entirely (counting rows that mention them gave 0). Insertion: there is no place for a new drink that has not been sold yet.

Step 4, decompose to 3NF

Each group that depends on its determinant moves to a table keyed by that determinant. 2NF: move the drink_id columns to drinks and the order_id columns to orders. 3NF: move the customer_phone columns to customers and the barista_id columns to baristas. The result:

CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    phone TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL,
    city  TEXT NOT NULL
);
CREATE TABLE baristas (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);
CREATE TABLE drinks (
    id            INTEGER PRIMARY KEY,
    name          TEXT NOT NULL UNIQUE,
    category      TEXT NOT NULL CHECK (category IN ('hot', 'cold')),
    price_halalas INTEGER NOT NULL CHECK (price_halalas > 0)
);
CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
    barista_id  INTEGER NOT NULL REFERENCES baristas(id),
    order_date  TEXT NOT NULL
);
CREATE TABLE order_items (
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    drink_id INTEGER NOT NULL REFERENCES drinks(id) ON DELETE RESTRICT,
    qty      INTEGER NOT NULL CHECK (qty > 0),
    PRIMARY KEY (order_id, drink_id)
);

Note the decisions: phone is a candidate key guarded by UNIQUE while id is the primary; CHECK guards category, price and quantity; and the three ON DELETE actions differ on purpose (Diagram 28).

Step 5, migrate the old data

INSERT INTO customers (phone, name, city)
SELECT DISTINCT customer_phone, customer_name, customer_city FROM sales_log;

INSERT INTO baristas (id, name)
SELECT DISTINCT barista_id, barista_name FROM sales_log;

INSERT INTO drinks (id, name, category, price_halalas)
SELECT DISTINCT drink_id, drink_name, drink_category, drink_price_halalas FROM sales_log;

INSERT INTO orders (id, customer_id, barista_id, order_date)
SELECT DISTINCT s.order_id, c.id, s.barista_id, s.order_date
FROM sales_log s JOIN customers c ON c.phone = s.customer_phone;

INSERT INTO order_items (order_id, drink_id, qty)
SELECT order_id, drink_id, qty FROM sales_log;

INSERT … SELECT DISTINCT lands each fact once. Then we fill orders by joining sales_log to the new customers table to take the customer’s id from the phone number. The new tables:

Step 6, check we lost nothing

We rebuild the flat shape by joining the five tables:

SELECT oi.order_id, d.name AS drink, c.name AS customer, c.city, b.name AS barista, oi.qty
FROM order_items oi
JOIN orders o    ON o.id = oi.order_id
JOIN drinks d    ON d.id = oi.drink_id
JOIN customers c ON c.id = o.customer_id
JOIN baristas b  ON b.id = o.barista_id
ORDER BY oi.order_id, d.id;

And in our full test (all twelve columns) the join’s result matched the original sales_log table row for row. The decomposition is lossless: what we split can be reassembled exactly.

Step 7, the anomalies are gone

Changing Noura’s city is now UPDATE customers SET city = 'Jeddah' WHERE phone = '0501110001', which changes exactly one row. Deleting an order no longer erases the drink or the customer. And recording a new drink is just INSERT INTO drinks … with no order at all.

Step 8, test the ON DELETE actions

With PRAGMA foreign_keys = ON: deleting customer 1 (SET NULL) left his orders with no customer:

DELETE FROM customers WHERE id = 1;
SELECT id, customer_id FROM orders ORDER BY id;

And deleting order 101 (CASCADE) removed its items automatically:

DELETE FROM orders WHERE id = 101;
SELECT order_id, drink_id FROM order_items ORDER BY 1, 2;

And a drink used in an order (RESTRICT) is refused by the engine:

Myth vs. Reality

Where Does Good Design Actually Matter?

Try it yourself

Quick Review Flashcards

Check Your Understanding