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. Non-key facts should depend directly on the key, rather than through another non-key fact. Suppose we stored the professor’s email alongside the professor ID in the offerings table. The offering ID identifies the professor, and the professor ID determines the email. The email describes the professor, so it belongs in the professors table, where changing it requires updating only one row.

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.