Normalization

I started designing the data model by splitting the spreadsheet into several tables. I followed a small set of rules while doing it, though I did not name them at the time. Now I will name the rules and show how they apply to the splits I made. These rules are called normalization rules, and applying them is called normalizing the database.

First normal form. A column holds one value per row. We applied this rule when we refused to pack several offerings into one column of the students table, or vice versa. We applied it again when we refused to pack several requirements into one column of the courses table. Splitting those into their own tables, with one value per row, is what first normal form asks for.

Second normal form. In a table whose primary key is a pair of columns, no other column depends on only part of that pair. The history entry table has no columns besides the two foreign keys, so this cannot come up there. But suppose I had added the student’s name to that table, to save a lookup. The student’s name depends only on the student ID, not on the pair of student ID and offering ID. It would then repeat once for every offering that student takes, and it would be the same problem I split the students table out to avoid in the first place. Second normal form is the rule that catches that before it happens.

Third normal form. No column depends on another column that is not the primary key. This explains why subject area got its own table in the last section. If a subject area later needed its own facts, a department chair or a budget code, storing those in the courses table would mean they depend on the subject area, not on the course number. Every course in that subject area would repeat them, and updating one would mean updating all of them, the same failure that split the professors table out of the offerings table in the first place.

All three rules follow the same principle, just applied at different points. A fact belongs in exactly one place, and that place is the table whose key identifies it. I have been following that principle since the first table was split off the spreadsheet.