Normalisation & schema design

Good SQL can't rescue a bad schema. Schema design means deciding which tables exist, which columns they have and how they relate. Normalisation is a step-by-step method for removing redundancy so that each fact is stored exactly once. That avoids the anomalies that slowly corrupt data.

Why redundancy hurts#

Imagine one big spreadsheet-style table of orders:

It looks convenient, but:

• Update anomaly: when Asha changes her email, you must update every one of her orders. Miss one and the data contradicts itself.
• Insert anomaly: you can't record a new customer until they place an order.
• Delete anomaly: delete Rohan's only order and you lose the fact that Rohan exists at all.
• Querying pain: "how many mice did we sell?" means string-searching the products text.

Normalisation fixes these problems one rule at a time.

First normal form (1NF): atomic values, no repeating groups#

Each column holds a single value, and each row is unique (has a key).

The products column holds a list, which breaks 1NF. So do repeating columns like product1, product2, product3. Move the repeating data into its own table, with one row per item:

Now "how many mice?" is a simple SUM(qty) ... WHERE product = 'Mouse'.

Second normal form (2NF): depend on the whole key#

1NF, plus every non-key column depends on the entire primary key, not just part of it.

2NF matters for tables with composite keys. Suppose order_lines has the key (order_id, product_id) and the columns qty, product_name and order_date:

• qty depends on both the order and the product. ✅
• product_name depends only on product_id. ❌ It belongs in products.
• order_date depends only on order_id. ❌ It belongs in orders.

Move each partial dependency to the table where it fully belongs.

Third normal form (3NF): no transitive dependencies#

2NF, plus non-key columns depend only on the key, not on other non-key columns.

In orders(id, customer_id, customer_email, city), customer_email depends on customer_id, which in turn depends on the order id. That's a transitive dependency. The email is a fact about the customer, so it belongs in customers.

A memorable summary: every non-key column must provide a fact about the key, the whole key, and nothing but the key.

There are higher forms (BCNF, 4NF, 5NF) for rarer situations, but a schema in 3NF is the practical goal for most applications.

The normalised design#

CREATE TABLE customers (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(255) NOT NULL UNIQUE,
    city       VARCHAR(60)
);

CREATE TABLE products (
    id         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL UNIQUE,
    price      DECIMAL(10,2) NOT NULL CHECK (price >= 0)
);

CREATE TABLE orders (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id INT UNSIGNED NOT NULL,
    ordered_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_orders_customer FOREIGN KEY (customer_id) REFERENCES customers (id)
);

CREATE TABLE order_items (
    order_id   INT UNSIGNED NOT NULL,
    product_id INT UNSIGNED NOT NULL,
    qty        INT UNSIGNED NOT NULL CHECK (qty > 0),
    unit_price DECIMAL(10,2) NOT NULL,          -- price at the time of sale (see below)
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_items_order   FOREIGN KEY (order_id)   REFERENCES orders (id) ON DELETE CASCADE,
    CONSTRAINT fk_items_product FOREIGN KEY (product_id) REFERENCES products (id)
);

The old spreadsheet is now one join away, and no fact is stored twice:

INSERT INTO customers (name, email, city) VALUES ('Asha', 'asha@x.com', 'Pune'), ('Rohan', 'rohan@x.com', 'Delhi');
INSERT INTO products (name, price) VALUES ('Keyboard', 50), ('Mouse', 20), ('Monitor', 200);
INSERT INTO orders (customer_id, ordered_at) VALUES (1, '2026-01-05'), (1, '2026-01-09'), (2, '2026-01-11');
INSERT INTO order_items VALUES (1, 1, 1, 50), (1, 2, 1, 20), (2, 3, 1, 200), (3, 2, 1, 20);

SELECT o.id, DATE(o.ordered_at) AS order_date, c.name, c.email,
       GROUP_CONCAT(p.name ORDER BY p.name SEPARATOR ', ') AS products,
       SUM(oi.qty * oi.unit_price) AS total
FROM orders o
JOIN customers c    ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON p.id = oi.product_id
GROUP BY o.id, o.ordered_at, c.name, c.email
ORDER BY o.id;

+----+------------+-------+-------------+-----------------+--------+
| id | order_date | name  | email       | products        | total  |
+----+------------+-------+-------------+-----------------+--------+
|  1 | 2026-01-05 | Asha  | asha@x.com  | Keyboard, Mouse |  70.00 |
|  2 | 2026-01-09 | Asha  | asha@x.com  | Monitor         | 200.00 |
|  3 | 2026-01-11 | Rohan | rohan@x.com | Mouse           |  20.00 |
+----+------------+-------+-------------+-----------------+--------+

Is order_items.unit_price a 3NF violation, since products.price already exists? No. They're different facts: the price today versus the price the customer actually paid. Historical values belong to the transaction. Spotting the difference between duplication and history is a core design skill.

Modelling relationships#

Junction tables often carry their own data: quantity, enrolment date, role, grade.

Practical design guidelines#

• Every table gets a primary key. Usually a surrogate id, plus UNIQUE constraints on natural identifiers (email, SKU).
• Consistent naming. Use lowercase snake_case, plural table names (orders), id for the key and <singular>_id for foreign keys (customer_id). Avoid reserved words.
• Use the right types and constraints (DECIMAL for money, NOT NULL by default, CHECK for ranges) and declare foreign keys.
• Lookup tables vs ENUM. If a list of values changes or has attributes (statuses with labels and sort order), use a table.
• Audit columns. created_at and updated_at on most tables cost little and help debugging.
• Soft deletes (deleted_at DATETIME NULL) keep history but complicate every query. Use them deliberately.
• Don't store what you can compute cheaply (order totals, ages) unless you have a measured performance reason.
• Avoid the EAV trap (entity, attribute, value tables for everything). It throws away types and constraints. For truly flexible attributes, a JSON column is usually better.

When to denormalise#

Normalisation optimises for correct writes. Sometimes reads matter more. Common, deliberate denormalisations:

• A cached orders.total, maintained by the application or a trigger, so order lists don't need to sum items.
• Summary/reporting tables rebuilt nightly by a scheduled event.
• Copying a value for history (the unit_price above).

Denormalise after you've measured a real performance problem, document why, and make sure something keeps the copies in sync.

A design checklist#

1. List the things (entities): customers, products, orders.
2. List each thing's facts (attributes) and pick its key.
3. Draw the relationships and their cardinalities (1–1, 1–N, N–M).
4. Check 1NF (no lists), 2NF (whole key) and 3NF (nothing but the key).
5. Add types, NOT NULL, UNIQUE, CHECK and foreign keys.
6. Write the queries your app needs against the design before building it, and add indexes for them (next lesson).

What's next#

A well-designed schema still needs the right indexes to stay fast as tables grow from hundreds of rows to hundreds of millions.