SQL › Organizing Tables the Right Way

Normalization: 1NF, 2NF and 3NF

Normalization is designing tables so that each fact is stored in one place. A fact stored many times can be changed in one place and missed in another. The normal forms are checks that find such repeats; each one fixes them by moving columns into a table of their own.

Syntax

Order of the checks: 1NF → 2NF → 3NF. Each form assumes the one before it holds.

Code
-- The fix is always the same shape: move the repeated fact to its own table and point to it
CREATE TABLE parent (parent_id INTEGER PRIMARY KEY, fact TEXT);
CREATE TABLE child (child_id INTEGER PRIMARY KEY,
                    parent_id INTEGER REFERENCES parent (parent_id));

Examples

One wide table

Every line repeats the customer, product and category facts.

Code
SELECT o.order_id, c.full_name, c.city, p.product_name, cat.category_name, oi.quantity
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
JOIN customers c ON c.customer_id = o.customer_id
JOIN products p ON p.product_id = oi.product_id
JOIN categories cat ON cat.category_id = p.category_id
ORDER BY o.order_id
LIMIT 6;

1NF: one value per cell

A customer with two phone numbers gets two rows in a table of their own, not a list in one cell. The shop's products.tags column is a list, so it breaks 1NF.

Code
SELECT product_id, tags FROM products WHERE product_id = 3;     -- several values in one cell
CREATE TABLE customer_phones (
  customer_id INTEGER REFERENCES customers (customer_id),
  phone VARCHAR(20),
  PRIMARY KEY (customer_id, phone)
);
INSERT INTO customer_phones VALUES (1, '9811000001'), (1, '9811000099');

2NF: depend on the whole key

In a table keyed by (order_id, product_id), a product_name column would depend on product_id alone. So order_items keeps only the id, and the name lives in products. unit_price does belong to the line: it's the price paid that day, and prices change.

Code
SELECT order_id, product_id, quantity, unit_price FROM order_items WHERE order_id = 2;   -- the lines
SELECT product_id, product_name FROM products WHERE product_id IN (9, 22);              -- each name, once

3NF: no facts about other columns

A category_name column in products would depend on category_id, not on the product. So it lives in categories, and renaming a category changes one row.

Code
UPDATE categories SET category_name = 'Games & Toys' WHERE category_id = 8;
SELECT p.product_name, c.category_name
FROM products p
JOIN categories c ON c.category_id = p.category_id
WHERE p.category_id = 8;                       -- every toy shows the new name

Look-alikes that are fine

orders.shipping_city is not a copy of the customer's city: an order can ship anywhere.

Code
SELECT o.order_id, c.city AS home_city, o.shipping_city
FROM orders o
JOIN customers c ON c.customer_id = o.customer_id
WHERE o.shipping_city <> c.city
ORDER BY o.order_id
LIMIT 5;

Denormalizing on purpose

orders.total_amount stores a total that could be worked out from the order lines, so reading it is fast, but it has to be kept in sync. v_order_summary shows where it doesn't match.

Code
SELECT order_id, computed_total, stored_total
FROM v_order_summary
WHERE computed_total <> stored_total
ORDER BY order_id;

Remember

• Store each fact in one place; point to it with a foreign key everywhere else.
• 1NF: one value per cell, no lists and no phone1, phone2 columns.
• 2NF: with a composite key, every other column needs the whole key.
• 3NF: a column that describes another non-key column belongs in that column's table.
• Denormalize only on purpose, such as stored totals, and keep the copies in sync.