When Not to Normalize Your Database

In Solutions by Design, "Responsible Denormalization," October 2000, I wrote that I’d tried to find valid reasons to denormalize production tables, but I had a hard time convincing myself that denormalization was ever a good idea. The only exception was in the case of reference or lookup tables, such as the Region table that Figure 2 in the main article shows. You can denormalize the Region table by removing the current primary key column, RegionID, and replacing it with the candidate key, Region. Now, the Region table is a unary (single-column) table that contains a list of unique values for Region. You’ll have to change tables that Region modifies—they no longer carry the value of RegionID. Instead, they contain the value for Region. You can successfully denormalize (or leave denormalized) many reference or lookup tables as long as the reference description (e.g., Region in the Region table) is a unique set of values.

If you want more information about database normalization and data modeling, the books in the following list are a good place to start.

Data-Modeling Books for Beginners

Michael J. Hernandez. Database Design for Mere Mortals: A Hands-On Guide to Relational Database Design (Addison-Wesley, 1997). Although this book isn’t about data modeling, it’s an excellent introduction to practical database design.

Jeffrey L. Whitten, Lonnie D. Bentley, and Kevin C. Dittman. Systems Analysis and Design Methods, 5th edition, (McGraw-Hill, 2000). This basic text about systems analysis and design concepts and techniques presents a superset of database modeling and design and gives a good view of how the database fits into the universe of a system.

Intermediate Data Modeling

Michael D. Reilly and Michelle A. Poolet. SQL Server 2000 Design and T-SQL Programming (Osborne/McGraw-Hill, 2000). This case study starts with requirements gathering, explains the steps of designing and planning a database, implements the design, and explains how to use the database. The book is for anyone who wants to learn about database design and planning and for anyone who wants to learn how to program in T-SQL against a SQL Server 2000 database.

Toby J. Teorey. Database Modeling and Design, revised edition (Morgan Kaufmann, 1998). If you're a data modeler with an interest in engineering, this book will tickle your fancy.

Graeme C. Simsion. Data Modeling Essentials, 2nd edition (Coriolis Group, 2000). This reference book about data-modeling techniques is easy to read and easy to use.

Advanced Data Modeling and Data Warehousing

Ralph Kimball, et al. The Data Warehouse Lifecycle Toolkit: Expert Methods for Designing, Developing, and Deploying Data Warehouses (Wiley, 1998). This book sets a standard for dimensional modeling. The text covers all aspects of data warehousing and is essential reading if you’re interested in the subject.

Len Silverston, W. H. Inmon, and Kent Graziano. The Data Model Resource Book: A Library of Logical Data Models and Data Warehouse Designs (Wiley, 1997). This valuable reference will help you understand why you should model a database to fifth normal form (5NF) and what kind of trouble you can get into if you don't.