5NF and Database Design
7 hours ago
- #logical modeling
- #database normalization
- #fifth normal form
- The article critiques the traditional teaching of the fifth normal form (5NF), arguing it's needlessly confusing and proposes a more practical design approach.
- It surveys examples from Wikipedia, Decomplexify, and Barry Johnson's blog, noting issues with contrived scenarios, particularly Wikipedia's unclear example.
- Emphasizes starting database design with a logical model from business requirements, leading to normalized physical schemas without directly using 5NF.
- Describes two key design patterns: the AB-BC-AC triangle (e.g., ice cream preferences) and the ABC+D star (e.g., musicians in concerts).
- Explains that physical table design involves choices like composite vs. synthetic primary keys based on uniqueness constraints.
- Extends examples to show patterns can coexist, such as adding specific preferences in the ice cream model.
- Discusses additional concepts like skill links in the musicians example, highlighting independence from performance records.
- References historical texts on normalization, suggesting teaching in reverse order, with 6NF as foundational.
- Concludes that 5NF is overcomplicated and advocates for a requirement-driven, logical model approach to avoid redundancy and anomalies.