Normalization

In short: Database normalization splits data into tables so that every fact is stored in one place, which removes update, insert and delete anomalies.

Problem

Excel came to the database

Remember the orders table from the first lesson of the course? Someone has moved it into the database as it was: one row for each product in an order, and next to it the date, the customer, their email and their city.

flat_orders

• order_idinteger *
• ordered_attext *
• customertext *
• emailtext *
• citytext
• producttext *
• pricereal *
• quantityinteger *

SQLempty database

SELECT * FROM flat_orders;

SELECT * FROM flat_orders;

▶ RunRestore the queryFresh database

11 rows, and only three customers. Maria's name and email are written 6 times. Look at her city in order 6, too.

The idea

Anomalies

When one fact is written in many places, every change is a risk. These problems are called anomalies. There are three kinds.

On update. Maria changes her email. A colleague fixes it only in her latest order:

SQLempty database

UPDATE flat_orders SET email = 'maria.i@example.com'
WHERE order_id = 6;

SELECT DISTINCT customer, email FROM flat_orders
WHERE customer = 'Maria Ivanova';

UPDATE flat_orders SET email = 'maria.i@example.com' WHERE order_id = 6; SELECT DISTINCT customer, email FROM flat_orders WHERE customer = 'Maria Ivanova';

▶ RunRestore the queryFresh database

Now the database says two things at once. Which email is right?

See it

Insert and delete

On insert. The shop starts selling oat flakes. Nobody has ordered them yet. Where do you write down their price?

SQLempty database

INSERT INTO flat_orders (product, price)
VALUES ('Oat flakes', 3.2);

INSERT INTO flat_orders (product, price) VALUES ('Oat flakes', 3.2);

▶ RunRestore the queryFresh database

A row without an order cannot go in. The product exists only once somebody buys it.

On delete. Elena cancels order 4, and it gets deleted. It was the only order with the frying pan:

SQLempty database

DELETE FROM flat_orders WHERE order_id = 4;

SELECT product, price FROM flat_orders
WHERE product = 'Frying pan 28 cm';

DELETE FROM flat_orders WHERE order_id = 4; SELECT product, price FROM flat_orders WHERE product = 'Frying pan 28 cm';

▶ RunRestore the queryFresh database

Together with the order, the fact that the shop sells a frying pan for 24.90 € is gone.

The idea

Normal forms

Arranging the tables so that every fact is in one place is called normalisation. The rules come in steps, the normal forms. Each one includes the one before.

First (1NF): one value per cell. No lists like “vegan, organic”, and no columns tag1, tag2, tag3.

SQLempty database

SELECT name, tags FROM products_sheet
WHERE tags = 'vegan';

SELECT name, tags FROM products_sheet WHERE tags = 'vegan';

▶ RunRestore the queryFresh database

Only the white bread. The wholegrain bread is vegan, but its cell says “vegan, organic”. With LIKE '%vegan%' you will find it, but you cannot count products by tag, fix “organic” everywhere or keep out a spelling mistake. Tags go in a link table, as in the first lesson of this module.

Second (2NF): every column depends on the whole key. This matters when the key has several columns:

order_lines

• PKorder_idinteger
• PKproduct_idinteger
• product_nametext *
• quantityinteger *

The product name depends only on product_id, not on the order. It will repeat in every order with that product. It belongs in products.

Third (3NF): columns depend only on the key, not on another column:

products

• PKidinteger
• nametext *
• category_idinteger *
• category_nametext *
• pricereal *

category_name is decided by category_id, not by the product. If the category is renamed, you have to fix every product in it. The name belongs in categories.

More precisely, if you are curious

The short formula for the three forms: every column depends on “the key, the whole key, and nothing but the key”.

There are higher forms too (Boyce-Codd, fourth, fifth), but in practice a table in third normal form usually no longer has these anomalies.

flat_orders breaks second normal form. The key of a row is the order and the product, while the date and the customer depend only on the order. It breaks the third as well: the city and the email depend on the customer, not on the order.

Try it

Which form?

1. In order_lines the key is (order_id, product_id), and product_name depends only on product_id. Which form is broken?

The secondThe thirdThe first

2. products has category_id and category_name. Which form is broken?

The secondThe firstThe third

Check

The idea

Splitting

You split flat_orders into the tables of the shop: customers, products, orders and order_items. You start with the ones that point to nothing.

INSERT can take its rows from a SELECT instead of VALUES. With DISTINCT, each product goes in once:

products

• PKidinteger
• nametext *
• pricereal *

SQLempty database

CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE,
  price REAL NOT NULL
);
INSERT INTO products (name, price)
SELECT DISTINCT product, price FROM flat_orders;

SELECT * FROM products;

CREATE TABLE products ( id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE, price REAL NOT NULL ); INSERT INTO products (name, price) SELECT DISTINCT product, price FROM flat_orders; SELECT * FROM products;

▶ RunRestore the queryFresh database

From 11 rows you get 6 products, each with an id. The price stays in the order rows as well, because that is the price it was bought at. Why that is not a repeat, you will see in the next lesson.

See it

Excel is dirty

The same for the customers:

customers

• PKidinteger
• nametext *
• emailtext *
• citytext

SQLempty database

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT
);
INSERT INTO customers (name, email, city)
SELECT DISTINCT customer, email, city FROM flat_orders;

CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, city TEXT ); INSERT INTO customers (name, email, city) SELECT DISTINCT customer, email, city FROM flat_orders;

▶ RunRestore the queryFresh database

An error. In order 6, Maria's city is “Sofiya”. DISTINCT compares the whole row, so it sees two customers with one email, and UNIQUE does not let them in. That is good: the rule caught the mistake instead of carrying it into the new database.

First you fix the data, then you split:

SQLempty database

UPDATE flat_orders SET city = 'Sofia' WHERE city = 'Sofiya';

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT
);
INSERT INTO customers (name, email, city)
SELECT DISTINCT customer, email, city FROM flat_orders;

SELECT * FROM customers;

UPDATE flat_orders SET city = 'Sofia' WHERE city = 'Sofiya'; CREATE TABLE customers ( id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT NOT NULL UNIQUE, city TEXT ); INSERT INTO customers (name, email, city) SELECT DISTINCT customer, email, city FROM flat_orders; SELECT * FROM customers;

▶ RunRestore the queryFresh database

Almost every move from Excel starts with a clean-up like this.

Try it

The orders

orders

• PKidinteger
• FKcustomer_idinteger *→ customers
• ordered_attext *

Products and customers are already split. Fill orders (id, customer_id, ordered_at) from flat_orders. The order number is order_id, and the customer is found by email.

SQLempty database

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  ordered_at TEXT NOT NULL
);

CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), ordered_at TEXT NOT NULL );

Check▶ Run

Exercise

Two tasks

1. What is left is the link table between orders and products.

order_items

• PKorder_idinteger→ orders
• PKproduct_idinteger→ products
• quantityinteger *
• unit_pricereal *

Fill order_items from flat_orders: the order number, the product id, the quantity and the price.

SQLempty database

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 REAL NOT NULL,
  PRIMARY KEY (order_id, product_id)
);

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 REAL NOT NULL, PRIMARY KEY (order_id, product_id) );

Check▶ Run

2. Now the report from the first lesson of the course takes one query. How much has each customer spent?

Show the name of each customer and the total of their purchases (quantity times price):

SQLempty database

Check▶ Run

Test

Check yourself

1. Maria changed her email, and it is on 40 rows of the table. A colleague fixed 38. What is this called?

A delete anomalyAn update anomalyAn insert anomaly

2. What breaks first normal form?

A city column that is sometimes NULLA tags column with the text “vegan, organic”A table without an id column

3. What does INSERT INTO products (name, price) SELECT DISTINCT product, price FROM flat_orders do?

Adds one row for each distinct pair of product and priceAdds a row to products for every orderNothing, INSERT cannot use SELECT

4. Why did splitting Maria's orders stop with a UNIQUE error?

Because an email cannot be UNIQUEBecause Maria has many ordersBecause her city is spelled differently on two rows, and DISTINCT saw her as two customers

Check

Start the lesson over

Related lessons

• Builds on: Constraints: rules for the data · Foreign keys: links between tables · Relationship types: 1:1, 1:N, M:N · ER diagram: from a description to a schema
• Continues in: Natural keys and denormalization · Document databases · The bakery, stage 1: the database and the tables
• Tasks on this topic: Designing a database