Skip to content

Intermediate · SQL in depth · topic 26 of 54 · 20 min

Normalisation and table design

Explanation

Normalisation means designing tables so each fact is stored only once. When the same fact sits in many rows, updates miss some copies and the data starts to disagree. The normal forms (1NF, 2NF, 3NF) are simple checks for this, and many-to-many links get their own junction table.

The messy spreadsheet

A coaching centre starts with one big sheet: one row per student per course. It is easy to begin with, and it quickly goes wrong.

studentphonecoursesfee
Priya98470 23456Python Basics, SQL for Beginners2999, 1999
Aman98200 34567SQL for Beginners1999
Zoya98400 45678Python Basics, SQL for Beginners, Spoken English2999, 1999, 1499
  • Update problem: the SQL course fee changes, and you must find and edit it in every row. Miss one and two fees exist.
  • Insert problem: you cannot add the new "Tally with GST" course until a student joins it.
  • Delete problem: if Aman leaves and you delete his row, you may lose the only record of a course.
Think of it like this: A good kitchen keeps one jar of salt in one place. If every shelf had its own little salt packet, you would never know which ones were empty or old. Normalisation is giving every fact one jar and one place, and letting everything else point to it.

1NF, 2NF and 3NF in plain words

FormRuleBroken here by
1NFOne value per cell; no lists, no columns like course1, course2"Python Basics, SQL for Beginners" in one cell
2NFEvery column depends on the whole key, not part of itIn a (student, course) row, the fee depends on the course only
3NFNo column depends on another non-key columnA city column next to a pincode: city depends on pincode

Fixing it gives three tables. students holds each student once. courses holds each course and its fee once. enrollments holds only pairs of ids: who joined what.

TableColumnsOne row means
studentsid, name, phoneOne student
coursesid, title, feeOne course
enrollmentsstudent_id, course_idThis student joined this course

One-to-many and many-to-many

One-to-many: one customer has many orders, so orders gets a customer_id column. Many-to-many: a student takes many courses and a course has many students. Neither table can hold the other's id, so you add a junction table (also called a link or join table) with one foreign key to each side. Its primary key is the pair, so the same student cannot enrol twice in one course.

sql
CREATE TABLE enrollments (
  student_id INTEGER NOT NULL REFERENCES students(id),
  course_id  INTEGER NOT NULL REFERENCES courses(id),
  enrolled_on TEXT,
  PRIMARY KEY (student_id, course_id)
);
A junction table: two foreign keys, and the pair is the primary key.
Careful: SQLite only enforces REFERENCES after PRAGMA foreign_keys = ON; on each connection. PostgreSQL and MySQL (InnoDB) enforce them always.

When to break the rules

Denormalising means storing a copy on purpose to make reads faster, like keeping order_count on the customers row instead of counting orders every time, or saving the price paid on each order item. The second one is not even a copy: it is a different fact (the price on that day), and it must not change when the catalogue price changes.

Tip: Start normalised. Denormalise only for a measured slow query, and decide who keeps the copy correct (a transaction, a trigger or a background job).

Watch the lesson

Normalisation and table design · 1/6

Normalisation and table design

Bad table design causes bugs that no amount of code can fully fix. Today: normalisation.

Prefer a video by a teacher? Find free videos on “database normalization 1nf 2nf 3nf explained with examples” on YouTube ↗

In real life: Price changes in an online shop

An online shop stored the product name and price directly on every order row. When the marketing team renamed "Steel Tiffin" to "Steel Tiffin Box 3 tier", old invoices showed the new name, and a price edit changed the totals of past orders in reports. The team moved to products, orders and order_items tables. order_items keeps the product_id plus the price_paid at the time of purchase, which is history and must not change, while the name always comes from products.

sql
CREATE TABLE order_items (
  order_id   INTEGER NOT NULL REFERENCES orders(id),
  product_id INTEGER NOT NULL REFERENCES products(id),
  qty        INTEGER NOT NULL CHECK (qty > 0),
  price_paid INTEGER NOT NULL,
  PRIMARY KEY (order_id, product_id)
);
price_paid is a fact about the sale, so it belongs on the order item.

Practice (0/3 done)

Task 1 · Code · SQL

Tables: students(id, name), courses(id, title, fee) and the junction table enrollments(student_id, course_id). List the titles of all courses Priya is enrolled in, in alphabetical order.

Task 2 · Code · SQL

Same tables. Show every course title with the number of students enrolled, including courses with no students (they must show 0). Most students first; when counts are equal, sort by title.

Task 3 · Practice

A tiffin service keeps one table: deliveries(customer_name, customer_phone, customer_area, meal_name, meal_price, delivery_date, delivery_boy_name, delivery_boy_phone). Redesign it in 3NF. Write the CREATE TABLE statements and one sentence on what each table holds.

Quick quiz

Question 1

1.A course fee is stored on every enrollment row. The fee changes and one row is missed. What is this problem called?

Question 2

2.Which design breaks first normal form (1NF)?

Question 3

3.Students take many courses and courses have many students. How do you model this?

Question 4

4.Why does order_items often store price_paid even though products already has price?

Question 5

5.When is denormalising a good idea?

Read more (free)

Pass the quiz (60% or more) or mark this topic as done when you are ready.

Your progress is saved on this device only.

Next: NoSQL and MongoDB →

← Previous: Transactions and ACID