Normalisation

Why it matters

Most databases that cause a DBA pain at 3 a.m. started as one wide spreadsheet-style table. A customer's email is typed into every order row, so when it changes, half the rows get updated and half do not. A product price lives in ten thousand places. Deleting the last order for a customer deletes the only record that the customer existed. These are called update, insert and delete anomalies, and normalisation is the design discipline that removes them.

As an administrator you will review schemas written by developers, and you will be asked why a report shows two different addresses for the same customer. Being able to spot a normalisation problem, and to explain the fix, is a core skill.

The core idea: one fact, one place

Normalisation is about functional dependencies: if you know the value of column A, do you always know the value of column B? If customer_email always tells you customer_name, then name depends on email, and the name should live in a table keyed by the customer, not repeated in every order.

The normal forms are a checklist, applied in order:

• First normal form (1NF): every column holds one atomic value, and there are no repeating groups. A column like products = 'pen, stapler, tape' or columns item1, item2, item3 break 1NF. Each item becomes its own row.
• Second normal form (2NF): 1NF, and every non-key column depends on the whole primary key. This only matters for composite keys. In an order_items table keyed by (order_id, sku), the column product_name depends on sku alone, so it belongs in products.
• Third normal form (3NF): 2NF, and no non-key column depends on another non-key column. In an orders table, customer_city depends on the customer, not on the order, so it moves to customers.

A popular summary: every non-key column must depend on "the key, the whole key, and nothing but the key". Boyce-Codd normal form (BCNF) tightens 3NF for some unusual overlapping keys; in day-to-day work, 3NF is the usual target.

Worked example

Here is a flat export from an online shop:

order_id | order_date | customer_email  | customer_name | customer_city | sku  | product_name | unit_price | quantity
1001     | 2026-03-01 | ana@example.com | Ana Silva     | Porto         | P-10 | Notebook     | 4.50       | 3
1001     | 2026-03-01 | ana@example.com | Ana Silva     | Porto         | P-22 | Gel pen      | 1.20       | 10
1002     | 2026-03-02 | ben@example.com | Ben Okafor    | Leeds         | P-10 | Notebook     | 4.50       | 1

Ask "what does each column depend on?":

• customer_name, customer_city depend on customer_email.
• product_name, unit_price depend on sku.
• order_date and the customer depend on order_id.
• quantity depends on the pair (order_id, sku).

Each group becomes a table:

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  name  TEXT NOT NULL,
  city  TEXT NOT NULL
);
CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  unit_price REAL NOT NULL
);
CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  order_date TEXT NOT NULL,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);
CREATE TABLE order_items (
  order_id INTEGER NOT NULL REFERENCES orders(order_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  quantity INTEGER NOT NULL,
  PRIMARY KEY (order_id, sku)
);

You can fill the new tables straight from the flat one with INSERT ... SELECT DISTINCT:

INSERT INTO customers (email, name, city)
SELECT DISTINCT customer_email, customer_name, customer_city FROM orders_flat;

INSERT INTO orders (order_id, order_date, customer_id)
SELECT DISTINCT f.order_id, f.order_date, c.customer_id
FROM orders_flat f JOIN customers c ON c.email = f.customer_email;

Now Ana's city is stored once. Changing it is one UPDATE on one row, and a product can exist before anyone orders it.

A note on prices and history

Real shops change prices. If unit_price lives only in products, old orders silently change value when the price changes. The usual fix is to store the price charged on order_items as well. That is not a normalisation mistake: the price at the time of sale is a different fact from today's list price. Good design asks what each column means, not just where duplicates appear.

Common mistakes

• Normalising by pattern, not meaning. Two columns that happen to hold the same text are not always the same fact (list price vs price charged).
• Using names as keys. Two customers can share a name. Use a surrogate key (customer_id) or a value that is truly unique, such as an email with a UNIQUE constraint.
• Forgetting the composite key. order_items needs PRIMARY KEY (order_id, sku) or the same product can appear twice in one order.
• Over-normalising reporting tables. Analytics warehouses often denormalise on purpose (star schemas) because they are read-heavy and loaded in batches. Normalise the transactional system; denormalise deliberately, and document it, for reporting.

Quick recap

• Normalisation removes update, insert and delete anomalies by storing each fact once.
• 1NF: atomic values, no repeating groups. 2NF: no partial dependency on a composite key. 3NF: no dependency between non-key columns.
• Find the dependencies, give each group its own table and key, and link the tables with foreign keys.

Try it

Open the Do tab and normalise a flat order export into four tables, then run the check query to prove nothing was lost.