Hands on: Database normalisation

In the last database column – Different types of nothing – I described two possible table structures for storing answers to multiple-choice questions and said both would hold data in third normal form (3NF), which means in first and second normal forms as well.

Robin Ball raised the question of whether the first table (see screen 1) was actually in 3NF. This is an excellent question, and one that goes straight to the heart of something that causes confusion in discussions about normalisation.

Looking at the table, the rule that springs to mind is one that determines whether a table is in first normal form (1NF). It’s the one stating there must be no repeating columns in a table. What are all those columns for holding the answers to individual questions, if not repetitive?

Let's look at another table (see screen 2) for comparison, which displays the same characteristic of apparently repeating columns.

This CourseAttend table stores details of which students are enrolled on various courses. There’s a CourseID column that identifies the course and then a series of columns labelled StudentID01, StudentID02, etc.

Note that the StudentID01 column does not imply that it refers to the first student to enrol for the course, nor to the best student - none of the StudentID columns imply position or rank.

So what information can we glean from this table? We can, for example, see that course CTW405 has three students enrolled, those with IDs 3, 7 and 8. All we can learn from the table is the IDs of students signed up for a course. (The value 0 in this table is being used to indicate ‘no student’.

Would it be better to use a null? See the last column mentioned above for more discussion on missing information).