Normalization

The logical design of the database, including the tables and the relationships between them, is the core of an optimized relational database. A good logical database design can lay the foundation for optimal database and application performance. A poor logical database design can impair the performance of the entire system.

Normalizing a logical database design involves using formal methods to separate the data into multiple, related tables. A greater number of narrow tables (with fewer columns) is characteristic of a normalized database. A few wide tables (with more columns) is characteristic of an unnormalized database.

Reasonable normalization will often improve performance. When useful indexes are available, the Microsoft® SQL Server™ query optimizer is efficient at selecting rapid, efficient joins between tables.

Some of the benefits of normalization include:

As normalization increases, so will the number and complexity of joins required to retrieve data. Too many complex relational joins between too many tables can hinder performance. Reasonable normalization often includes few regularly executed queries that use joins involving more than four tables.

Sometimes the logical database design is already fixed and total redesign is not feasible. Even then, however, it might be possible to normalize a large table selectively into several smaller tables. If the database is accessed through stored procedures, this schema change could take place without affecting applications. If not, it might be possible to create a view that hides the schema change from the applications.

In relational database design theory, normalization rules identify certain attributes that must be present or absent in a well-designed database. While a complete discussion of normalization rules goes well beyond the scope of this topic, there are a few rules that can help you achieve a sound database design:

  


(c) 1988-98 Microsoft Corporation. All Rights Reserved.