Fourth normal form

• 25 May 2025
• Databases

For some reason 4NF is widely considered obscure, unimportant, rarely encountered, impractical, while 3NF is regarded as enough. I'm not sure where this misunderstanding comes from. I'm not even exaggerating, it's an old issue, here's a paper from 1992: "The practical need for fourth normal form".

If I were to write a database course, I'd start with 4NF, as it's the easiest one of them all, it can be explained visually by showing drop-down lists on an html form, where the first field determines what's shown in other options.

Redundant combinations

Let's say we have a page for students to choose a course, an instructor, and available languages. The options could look like this when you choose database theory:

The corresponding table would be:

Or we could break it down and let students choose an instructor and a language separately:

The tables would be:

But what if you choose philosophy, and teaching it is too hard in a foreign language? Kris can only do it in English, and Sophia only in French:

The corresponding part of the table would be:

Then we can't break it down like we were going to before. If we did break it down, we would have to convey the information that, e.g., Sophia doesn't teach philosophy in English some other way, like by showing an error message and politely asking to pick another option. Or we can decide to let students choose and listen to Kris and Sophia awkwardly philosophizing in their non-native languages.

If we were talking about relations, this would be about whether or not to break down (course, instructor, language) into (course, instructor) and (course, language) in order to avoid pairing every instructor with every language in the table.

That's all 4NF is about: if we can break a relation down to avoid redundant combinations, then we should, because it's a 4NF violation. If we can't, then we shouldn't.

Lots of combinations

The following example is slightly contrived, because in reality this would be much more complex and interesting, but it illustrates the point. If we were designing a schema for a sportswear store, we would probably end up with something like this:

And not this, which is bad UX and violates 4NF:

Here the relations would be (product, size) and (product, color) instead of (product, size, color), because the latter would have all the combinations of sizes and colors redundantly. Imagine the number of entries in this list if there were even more sizes and colors, and if we added printed images and other features to the relation.

Maintaining integrity

Why do we even care about 4NF violation? Because, for example, changing an available color from green to yellow would have to be done in multiple rows instead of one.

It doesn't seem like a big deal, but on a larger scale maintaining integrity in application logic rather than in schema design is error-prone and without a reason should be avoided. Usually the reason is performance, but it has to be measured first before making trade-offs.

Why are some explanations so weird?

Lastly, there are examples of 4NF violation that can be found on the internet that are outright weird and confusing, because they are taken out of context and teach nothing.

Here are two absolutely independent lists that simply shouldn't be in one table side by side, namely student's courses and hobbies:

Sometimes presented like this, which is cryptic, these combinations make no sense:

No one in their right mind would ever design a table like this. Yet this is discussed in some guides, and surprisingly or not, there is a reason. It's just never given, and as it happens, it's historical. The following great blog post sheds light: "Normalization is not a process".

Basically, tables like this are artifacts of a particular process, which is taught in traditional courses, often without a clear explanation why it is taught at all.

Well, back in the day, when relational database theory was being formalized, there had to be a way to migrate from existing storage systems without losing information. One approach is to take all the attributes that can be found in the application, put them in one huge relation, and then iteratively break it down into smaller relations until there's nothing left to split. The process itself is fine and really useful in certain circumstances, but it's purely mechanical and doesn't give much insight. And along the way it sometimes produces intermediate tables like the ones shown above before reaching the final schema.

We don't have to stick to this process when we don't migrate from hierarchical storage. When we design schemas, relations that just make sense turn out to be fine most of the time.

Good news

Because it's so intuitive to avoid redundant combinations, a lot of schemas are already in 4NF completely by accident.

Another nice thing to know about 4NF is "only in rare situations does a 4NF table not conform to 5NF", quote from wikipedia. One less thing to worry about.