Normalization

Normalization is the process of organising tables so that each fact is stored exactly once. It prevents the update, insert and delete anomalies that plague badly designed databases. You don't need the theory to use SQL, but understanding the first three normal forms will make your table designs far better.

A badly designed table

CREATE TABLE orders_flat (
  order_id INTEGER,
  order_date TEXT,
  customer_name TEXT,
  customer_email TEXT,
  products TEXT,
  total DECIMAL(10, 2)
);

INSERT INTO orders_flat VALUES
  (1, '2026-01-05', 'Ana Pereira', 'ana@example.com', 'Mug, Desk Lamp', 46.50),
  (2, '2026-01-09', 'Ana Pereira', 'ana.p@example.com', 'Train Set', 39.90),
  (3, '2026-01-12', 'Ben Carter', 'ben@example.com', 'Mug', 12.50);

SELECT * FROM orders_flat;

It looks convenient, but it has serious problems:

• Update anomaly: Ana's email is stored twice, and the copies already disagree. Which is right?
• Insert anomaly: you can't record a customer who hasn't ordered yet, or a product nobody has bought.
• Delete anomaly: deleting Ben's only order deletes everything we know about Ben.
• Unqueryable lists: "how many mugs were sold?" requires parsing text.
• Derived data: total can drift out of sync with the items.

First normal form (1NF): one value per cell

Each column holds a single, atomic value — no comma-separated lists, no phone1, phone2, phone3 columns. Repeating data goes into its own rows, usually in a separate table. Above, products breaks 1NF.

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

In a table with a composite key, every non-key column must depend on the whole key, not just part of it. If order_items (key: order_id + product_id) also stored product_name, the name would depend only on product_id — it belongs in products.

Third normal form (3NF): depend only on the key

Non-key columns must not depend on other non-key columns. If orders stored customer_email, the email would depend on customer_id, not on the order — it belongs in customers. A popular summary: every column should describe "the key, the whole key, and nothing but the key".

The normalised design

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price DECIMAL(8, 2) NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), order_date TEXT NOT NULL);
CREATE TABLE order_items (
  order_id INTEGER NOT NULL REFERENCES orders(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL,
  unit_price DECIMAL(8, 2) NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

INSERT INTO customers VALUES (1, 'Ana Pereira', 'ana@example.com'), (2, 'Ben Carter', 'ben@example.com');
INSERT INTO products VALUES (1, 'Mug', 12.50), (2, 'Desk Lamp', 34.00), (3, 'Train Set', 39.90);
INSERT INTO orders VALUES (1, 1, '2026-01-05'), (2, 1, '2026-01-09'), (3, 2, '2026-01-12');
INSERT INTO order_items VALUES (1, 1, 1, 12.50), (1, 2, 1, 34.00), (2, 3, 1, 39.90), (3, 1, 1, 12.50);

SELECT p.name, SUM(oi.quantity) AS sold
FROM order_items AS oi JOIN products AS p ON p.id = oi.product_id
GROUP BY p.name;

Each fact lives in one place, and questions that were awkward before are simple joins and aggregates. This is exactly the structure of the course's demo database.

When to denormalise

Normalization optimises for correct writes. Sometimes reads matter more, and teams deliberately duplicate data:

• Historical snapshots: order_items.unit_price copies the price at the time of sale on purpose — it's a different fact from today's price.
• Reporting and analytics: data warehouses use wide, denormalised "star schemas" for fast aggregation.
• Performance: a cached total or count can avoid expensive joins, if something (a trigger, lesson 48, or application code) keeps it correct.

Start normalised, then denormalise specific things for measured reasons.

Try it yourself

This table stores a student's courses as a list. Redesign it into normalised tables and query how many students take each course.