Professional level · Lesson 19 of 20

Designing tables: normalization

Why one big table goes wrong, and 1NF, 2NF and 3NF without the jargon.

10 minutes2 games

This table keeps everything in one place. It looks convenient, but run the query and see the problem:

SQL

SELECT student, city, course FROM enrollments_flat WHERE student = 'Nour';

-- Which city does Nour live in?
SELECT DISTINCT city FROM enrollments_flat WHERE student = 'Nour';

Nour's city is stored twice, once wrongly (Gizah). Repeated facts drift apart over time. There are three classic problems:

Update anomaly: if Nour moves, every row of hers must change; miss one and the data contradicts itself.
Insert anomaly: you can't add a new course until someone enrolls in it.
Delete anomaly: delete the last React enrollment and the course's price is lost with it.

Form	The rule, simply
1NF	one value per cell, no repeating columns like `course1, course2`
2NF	every column depends on the whole key, not part of it
3NF	no column depends on another non-key column (the city belongs to the student, not the enrollment)

One sentence sums them up: store each fact once, in the table it describes.

The fix is the design we've used all along: students holds the city, courses holds the price, and enrollments holds only what belongs to the enrollment itself (progress and the date).

Exercise

In enrollments_flat: find students whose city is recorded in more than one way. Show student and the number of different cities as cities.

Expected result:

student | cities
Nour | 2

SQL

SELECT student
FROM enrollments_flat

Quick question

In the flat table, what does course_price really depend on?

Learn more from

Played 0 of 2 games

SQL