Normalization Concerns

Normalization is a science of compromises. Although it greatly increases the performance margin of relational databases, extensive normalization that results in several related tables can become a big performance problem and a victim of its own normalization success. Normalization is an issue that must be balanced.

Consider our example again. The query retrieving every available detail for order number 2874 is:

SELECT *
FROM dbo.Order
WHERE Order = 2874

This is straightforward. After we’ve put the database into 3NF, the query to achieve this same result is:

SELECT dbo.Order.Order, dbo.OrderDetail.Quantity,
  dbo.OrderDetail.Total, dbo.Product.ProductDescription,
  dbo.Account.Customer
FROM dbo.Order INNER JOIN
    dbo.OrderDetail ON
    dbo.Order.Order = dbo.OrderDetail.Order INNER JOIN
    dbo.Product ON
    dbo.OrderDetail.Product = dbo.Product.Product 
      INNER JOIN
    dbo.Account ON
    dbo.Order.AccountNumber = dbo.Order.AccountNumber
WHERE (dbo.Order.Order = 2874)

The query requires us to construct complex joins in order to achieve the same result. Joins are more efficient when they are kept simple. However, there are many things that can form part of an order in the real world. Consider issues like shipment method and customer contact information that are tied to an order. Retrieving all this data using joins causes performance issues.

When normalizing, always maintain a plan as to how the normalized table is going to be used and the depth of related tables that you want to break it into.