Normalizing Logical Database Design

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 SQL Server optimizer is very efficient at selecting rapid, efficient joins between a reasonable number of tables.

These are some of the benefits of normalization:

• Faster sorting and index creation because tables are narrower.
• More clustered indexes are allowed because there are more tables.
• Narrower and more compact indexes.
• Fewer indexes per table, helping INSERT, UPDATE and DELETE performance.
• Fewer NULLs and less redundant data, increasing database compactness.
• Improved concurrency when running DBCC diagnostics, because the necessary table locks will affect less data.

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 very few regularly executed queries that use more than four-way joins.

Sometimes the logical database design is already fixed and total redesign is not feasible. Even then, however, it might be possible to selectively normalize a large table into several smaller tables. If access to the database is conducted 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.