Normalization: storing each fact once

Normalization means storing each fact in one place only. Every other place that needs the fact points to it.

Your class schedule in the university's online enlistment system lists each course with its title, such as Operations Management for MGT 45. When a department renames the course, every student's schedule shows the new title. Behind the page, the database holds one row for each enlistment.

Schedule pageWhat you see
EnlistmentsA table with one row per enlistment
CoursesA table with one row per course
Think first. The database holds one row per enlistment. Where could it keep the course title? Write down two places and what a rename would change in each.
One answer is to copy the title into every enlistment row. The other is to keep it once on the course and have each enlistment point to the course, which the database reads with a join. Collect a few answers before anyone opens the next step. Some students will say the second is more work, which is true and is covered in the last step.

The same enlistments are stored in two layouts. The course gets a new title. Press Rename MGT 45 and compare the rows each layout writes. Then press the second button to see what a missed row looks like.

Rows written, flat
—
Rows written, split
—
One flat table  enlistments
Split in two  courses + enlistments
More detail

Change how many students are enlisted

The flat table repeats the title once for each enlistment, and the split version stores it once. Raise the slider to 400 and think about running a script that makes 400 separate changes to a live database.

Store each fact once, and point to it from every place that needs it. A title kept in one row has one value. A title copied into four hundred rows can differ from row to row, and nothing in your code will notice, because each row is valid on its own.

You can now spot a fact stored in many places and split it into tables.

More detail

The cost of splitting

Both designs are valid. The flat table can show one student's schedule without a join, which is why people propose it, and the split design needs the join each time it reads. The size of that cost is measured in The N+1 query problem.

AI-generated schemas

A schema generated by an AI tool can work and still repeat facts like this. The tool will not warn you.