Why design matters
A database that works on day one can become painful by month six. The common cause is duplicated data. Imagine one big table of orders that repeats the customer's name, email and city on every row. Three problems follow:
- Update anomaly. The customer moves city. You must change dozens of rows, and if you miss one, the data contradicts itself.
- Insert anomaly. You cannot record a new customer until they place an order, because there is no row to hold them.
- Delete anomaly. Deleting a customer's last order erases all trace of the customer.
Normalisation is the discipline of arranging data so each fact is stored in exactly one place. It is a set of guidelines called normal forms, and the first three cover almost every practical case.
A messy starting point
Here is a table a spreadsheet user might build for a small shop:
CREATE DATABASE IF NOT EXISTS practice;
USE practice;
DROP TABLE IF EXISTS flat_orders;
CREATE TABLE flat_orders (
order_id INT,
order_date DATE,
customer_name VARCHAR(60),
customer_email VARCHAR(100),
customer_city VARCHAR(50),
items VARCHAR(200)
);
INSERT INTO flat_orders VALUES
(1, '2024-05-01', 'Asha Verma', 'asha@example.com', 'Delhi', 'Mouse x1, Keyboard x1'),
(2, '2024-05-03', 'Asha Verma', 'asha@example.com', 'Delhi', 'Mug x4'),
(3, '2024-05-04', 'Ben Carter', 'ben@example.com', 'London', 'Lamp x2');
SELECT * FROM flat_orders;+----------+------------+---------------+------------------+---------------+-----------------------+
| order_id | order_date | customer_name | customer_email | customer_city | items |
+----------+------------+---------------+------------------+---------------+-----------------------+
| 1 | 2024-05-01 | Asha Verma | asha@example.com | Delhi | Mouse x1, Keyboard x1 |
| 2 | 2024-05-03 | Asha Verma | asha@example.com | Delhi | Mug x4 |
| 3 | 2024-05-04 | Ben Carter | ben@example.com | London | Lamp x2 |
+----------+------------+---------------+------------------+---------------+-----------------------+Questions this design cannot answer well: how many mice were sold? (the items are text), what happens when Asha moves? (rows 1 and 2 both change), and which products exist that nobody has ordered?
First normal form (1NF)
Each cell holds one value, and there are no repeating groups. The items column stores a list inside one cell. You cannot count, join or index it. The fix is one row per item, in its own table:
order_items: order_id | product | quantityRule of thumb: if you find yourself writing comma-separated values or columns like item1, item2, item3, move them to a related table.
Second normal form (2NF)
Every non-key column depends on the whole primary key. This matters when the key has more than one column. If the key of the items table were (order_id, product) and it also stored order_date, the date would depend only on order_id, which is just part of the key. Move it to the orders table. With single-column surrogate keys, 2NF is usually satisfied automatically.
Third normal form (3NF)
Non-key columns depend on the key, the whole key, and nothing but the key. In flat_orders, customer_email and customer_city describe the customer, not the order. They depend on the customer, who is only indirectly identified by the order. This is a transitive dependency. The fix is a customers table; orders then refer to it.
The normalised design
DROP TABLE IF EXISTS n_order_items;
DROP TABLE IF EXISTS n_orders;
DROP TABLE IF EXISTS n_products;
DROP TABLE IF EXISTS n_customers;
CREATE TABLE n_customers (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(60) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
city VARCHAR(50) NOT NULL
);
CREATE TABLE n_products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(60) NOT NULL UNIQUE,
price DECIMAL(8,2) NOT NULL
);
CREATE TABLE n_orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
FOREIGN KEY (customer_id) REFERENCES n_customers (id)
);
CREATE TABLE n_order_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
PRIMARY KEY (order_id, product_id),
FOREIGN KEY (order_id) REFERENCES n_orders (id),
FOREIGN KEY (product_id) REFERENCES n_products (id)
);
INSERT INTO n_customers (name, email, city) VALUES ('Asha Verma', 'asha@example.com', 'Delhi'), ('Ben Carter', 'ben@example.com', 'London');
INSERT INTO n_products (name, price) VALUES ('Mouse', 24.99), ('Keyboard', 89.50), ('Mug', 18.75), ('Lamp', 32.00);
INSERT INTO n_orders (customer_id, order_date) VALUES (1, '2024-05-01'), (1, '2024-05-03'), (2, '2024-05-04');
INSERT INTO n_order_items VALUES (1, 1, 1), (1, 2, 1), (2, 3, 4), (3, 4, 2);Now every fact lives in one place. Asha's city is stored once, so a move is a single UPDATE. A customer can exist before ordering. Products can exist with no orders. The old report is a join away:
SELECT o.id, c.name, c.city, p.name AS product, oi.quantity, oi.quantity * p.price AS line_total
FROM n_orders o
JOIN n_customers c ON c.id = o.customer_id
JOIN n_order_items oi ON oi.order_id = o.id
JOIN n_products p ON p.id = oi.product_id
ORDER BY o.id, p.name;+----+------------+--------+----------+----------+------------+
| id | name | city | product | quantity | line_total |
+----+------------+--------+----------+----------+------------+
| 1 | Asha Verma | Delhi | Keyboard | 1 | 89.50 |
| 1 | Asha Verma | Delhi | Mouse | 1 | 24.99 |
| 2 | Asha Verma | Delhi | Mug | 4 | 75.00 |
| 3 | Ben Carter | London | Lamp | 2 | 64.00 |
+----+------------+--------+----------+----------+------------+And the update anomaly is gone:
UPDATE n_customers SET city = 'Gurugram' WHERE email = 'asha@example.com';
SELECT name, city FROM n_customers;+------------+----------+
| name | city |
+------------+----------+
| Asha Verma | Gurugram |
| Ben Carter | London |
+------------+----------+Types of relationship
| Relationship | Example | How to store it |
|---|---|---|
| One to many | one customer, many orders | foreign key in the "many" table (orders.customer_id) |
| Many to many | students and courses | a junction table with two foreign keys |
| One to one | user and profile | foreign key with a UNIQUE constraint, or share the primary key |
The order items table above is really a junction between orders and products, with an extra column (quantity). Its composite primary key stops the same product appearing twice in one order. Here is the classic many-to-many case:
DROP TABLE IF EXISTS enrolments;
DROP TABLE IF EXISTS courses;
DROP TABLE IF EXISTS students;
CREATE TABLE students (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(60) NOT NULL);
CREATE TABLE courses (id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(80) NOT NULL);
CREATE TABLE enrolments (
student_id INT NOT NULL,
course_id INT NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students (id),
FOREIGN KEY (course_id) REFERENCES courses (id)
);
INSERT INTO students (name) VALUES ('Riya'), ('Sam');
INSERT INTO courses (title) VALUES ('SQL'), ('HTML');
INSERT INTO enrolments VALUES (1, 1), (1, 2), (2, 1);
SELECT s.name, c.title FROM enrolments e JOIN students s ON s.id = e.student_id JOIN courses c ON c.id = e.course_id ORDER BY s.name, c.title;+------+-------+
| name | title |
+------+-------+
| Riya | HTML |
| Riya | SQL |
| Sam | SQL |
+------+-------+Design habits that help
- Give every table a primary key, usually a surrogate
id. - Name things consistently: singular column names,
table_idfor foreign keys. - Prefer a lookup table to a long
ENUMwhen the list of values changes or carries extra data (for example astatusestable). - Store each piece of information in the most precise type: dates in date columns, money in
DECIMAL. - Do not store values you can calculate cheaply and reliably, such as an order total that is the sum of its lines, unless you measure a need.
- Use constraints to encode rules in the database.
When to denormalise
Normalised designs need joins, and joins cost time on very large data. Reporting tables, caches and counters are sometimes deliberately duplicated, such as storing orders_count on the customer row. That is denormalisation. It trades extra storage and the risk of inconsistency for faster reads. Normalise first, measure, and denormalise only where a real query needs it, and plan how the copies stay in sync.
Common mistakes
- Comma-separated lists in a column, breaking 1NF.
- Repeating customer or product details in every order row.
- Columns named
phone1,phone2,phone3. - Normalising to the point of absurdity, with a table for every tiny value. Balance clarity and practicality.
- Using a many-to-many without a junction table.
Exercise
A library keeps a table loans(loan_id, member_name, member_email, book_title, book_author, loan_date). List the problems and propose a set of tables. Then write the CREATE TABLE statements for the members and books tables and a loans junction.
Show one possible answer
Member and book details repeat on every loan (3NF violation), so a member who changes email must be updated in many rows, and a book with no loans cannot be recorded. Split into members, books and loans:
DROP TABLE IF EXISTS lib_loans;
DROP TABLE IF EXISTS lib_books;
DROP TABLE IF EXISTS lib_members;
CREATE TABLE lib_members (id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(60) NOT NULL, email VARCHAR(100) NOT NULL UNIQUE);
CREATE TABLE lib_books (id INT PRIMARY KEY AUTO_INCREMENT, title VARCHAR(120) NOT NULL, author VARCHAR(80) NOT NULL);
CREATE TABLE lib_loans (
id INT PRIMARY KEY AUTO_INCREMENT,
member_id INT NOT NULL,
book_id INT NOT NULL,
loan_date DATE NOT NULL,
FOREIGN KEY (member_id) REFERENCES lib_members (id),
FOREIGN KEY (book_id) REFERENCES lib_books (id)
);A member can borrow many books and a book can be borrowed many times, so loans is the junction between them. It has its own id because the same member may borrow the same book more than once.
Key points
- Store each fact once. Duplicated data makes updates, inserts and deletes error-prone (anomalies).
- 1NF: one value per cell and no repeating groups. 2NF: every non-key column depends on the whole key. 3NF: non-key columns depend on nothing but the key.
- One-to-many uses a foreign key in the 'many' table; many-to-many needs a junction table holding two foreign keys.
- Denormalising (duplicating on purpose) can speed up reads, but do it deliberately, after measuring.