5NF and Database Design
This article deconstructs the complexities of Fifth Normal Form (5NF), arguing that traditional teaching methods are unnecessarily confusing and that a solid logical design approach naturally leads to normalized schemas.
One of the goals of this publication is to deconstruct the traditional ways of teaching basic topics in relational databases. Previously we discussed the fourth normal form (4NF): “Historically, 4NF explanations are needlessly confusing”. Let’s discuss the ultimate beast: fifth normal form (5NF). Often it’s presented even more confusingly than 4NF is, and we can show that this presentation is unnecessary, and the confusion is completely artificial.
Instead of starting with confusing tables, we must begin with the logical model that corresponds to the actual business scenario. When the logical model is complete, we can build a physical schema, using a textbook table design strategy. The result would be perfectly normalized: no redundancy, and no anomalies. Two logical design patterns arise around 5NF: the AB-BC-AC triangle (the “ice cream” example), and the ABC+D star pattern (the “musicians” example).
In the ice cream example, we have three entities: Brand, Flavour, and Friend. By defining the M:N relationships between each pair (Brand-Flavour, Friend-Brand, Friend-Flavour), we create three junction tables. This approach, rooted in logical modeling, ensures the database is in 5NF without the designer needing to struggle with abstract academic definitions. Ultimately, good design comes from understanding business requirements and mapping them to a logical structure that preserves normalization naturally.
Source: Hacker News
