← SQL roadmap

Topic 25 of 52 · Advanced

Normalization & Schema Design

1NF, 2NF, 3NF, BCNF and when to denormalise.

Concept

Normalization organises tables to remove redundancy and update anomalies. 1NF: atomic values, no repeating groups.

2NF: no partial dependency on part of a composite key. 3NF: no transitive dependency (non-key → non-key). BCNF: every determinant is a candidate key.

Analytics systems often denormalise (star schema: fact table + dimension tables) to make reads faster.

Cheat sheet

FormRule
1NFAtomic values, unique rows
2NF1NF + no partial dependency
3NF2NF + no transitive dependency
BCNFEvery determinant is a candidate key

Code (SQL · MySQL 8)

-- Bad: order(order_id, customer_name, customer_city, product_list)
-- Good (3NF):
CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2));
CREATE TABLE order_items (
  order_id INT, product_id INT, qty INT,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders(id),
  FOREIGN KEY (product_id) REFERENCES products(id)
);

Remember

  • Many-to-many needs a junction table.
  • OLTP → normalise; OLAP → star schema.
  • Anomalies: insert, update, delete.

Interview questions

Explain 3NF with an example.

In employee(emp_id, dept_id, dept_name), dept_name depends on dept_id, not emp_id. Move dept_name to a departments table.

When would you denormalise?

For read-heavy reporting where joins are expensive, accepting extra storage and update complexity.

Practise SQL with the AI mentor

Lesson test

Take an 8-question test on “Normalization & Schema Design” — concepts, output prediction, bug spotting and interview questions.

Can't find a SQL topic you need for exams or placements? Request it and we'll add it.