THE COSTLY ILLUSION: Normalization, Integrity and Performance (PRACTICAL DATABASE FOUNDATIONS Book 2)

Note: This paper assumes familiarity with the concepts and terminology introduced in paper #1, Business Modeling For Database Design in this series.

A core database design principle is the Principle of Full Normalization (PFN). Database designs that do not adhere to it present certain practical drawbacks for data manipulation, integrity enforcement and, consequently, for the correct manipulation of data and interpretation of results. Despite the plethora of information on the subject (not all of it correct, or well explicated), the subject is still poorly understood.

Paper #1 in this series, Business Modeling for Database Design, outlines a methodology that implicitly produces fully normalized databases. But due either to inadvertent errors, or to intentional “denormalization for performance”, PFN violations occur frequently. They impose considerable and insidious costs to which many data professionals are oblivious. Data redundancy and the risk of inconsistent databases is only one of them, albeit a major one.

Explicit further normalization should be necessary only for database design repair, when databases were poorly designed, to eliminate the drawbacks.

This paper explains in easy to understand language:
· The kinds of PFN violation possible;
· The undesirable properties of PFN violations and their costs;
· How to repair the design and eliminate the drawbacks;
· Why denormalization for performance is a dangerous illusion.


Table of Contents

Introduction

1. R-tables, Keys and Dependencies

2. Normalization and Normal Forms

3. Further Normalization As Design Repair
3.1. Join Dependencies
3.2. “The Whole Key” and 2NF
3.3. “Nothing But the Key” and 3NF
3.4. “The Whole Key” and BCNF
3.5. Multivalued Dependencies and 4NF
3.6. Interval Data and 6NF

4. “Denormalization For Performance”
4.1. The Logical-Physical Confusion
4.2. Redundancy Control
4.3. JDC’s and SQL
4.4. The Real Problem and Solution

5. Conclusion and Recommendations

References