Demystifying Fifth Normal Form in Database Design

In this article, I aim to unravel the complexities surrounding the fifth normal form (5NF) in relational databases, which is often presented in a confusing manner. By using practical examples, I demonstrate that much of the confusion is unnecessary and can be avoided by starting with a logical model based on business requirements.

## Understanding 5NF

To understand 5NF, I critique the inadequacies of the Wikipedia example, which uses a convoluted scenario involving salespeople, brands, and product types. This example imposes unrealistic business rules, such as a salesperson being required to sell all product types from a brand if they sell any. This approach is impractical and doesn't aid in understanding database design.

## Logical Model Approach

Instead of starting with such examples, I suggest beginning with a logical model that reflects actual business scenarios. This involves creating a logical schema and then designing a physical table schema that is perfectly normalized, eliminating redundancy and anomalies.

## AB-BC-AC Triangle: The Ice Cream Example

I illustrate the AB-BC-AC triangle pattern using an ice cream example. Here, brands produce various flavors, and friends have preferences for both brands and flavors. The logical model includes three anchors: Brand, Flavour, and Friend, each with corresponding tables. The relationships between these entities form a triangle, with each link having M:N cardinality. This example is straightforward and avoids the pitfalls of the Wikipedia example.

## ABC+D Star Pattern: The Musicians Example

The ABC+D star pattern is explained through a musicians and concerts scenario. Here, the challenge is to record which musicians played which instruments at concerts. This involves four anchors: Concert, Instrument, Musician, and Performance. The performance serves as a central link, connecting the other three anchors in a star pattern. This pattern is more complex due to the need for uniqueness constraints, which can be managed using either synthetic or composite primary keys.

## Practical Table Design

In practical table design, the choice between using a synthetic ID or a composite primary key depends on business requirements. For example, in a scenario where uniqueness is required, a composite primary key can be used. However, if there is no uniqueness requirement, a synthetic ID is preferable.

## Extending Examples

I extend the ice cream example to accommodate specific preferences, such as a friend liking a particular flavor from a specific brand. This involves adding a new anchor called Preference and creating a new table to capture these specific links, demonstrating the flexibility of the logical model approach.

## Conclusion

By starting with business requirements and logical schema, we can design fully normalized tables without the need for artificial decomposition into 5NF. The AB-BC-AC triangle and ABC+D star patterns naturally emerge from this process, providing a clear and practical approach to database design.