SQL Database Design » 14. Normalization

14. Normalization

Table of Contents

Ideally, one relation translates to a single table. In practice however, you often find two or more relations crammed together in a single table. This is usually caused by misconceiving distinct entity types, which should be two tables connected to each other via a one-to-one relationship. As Pascal put it: Note: Loosely, a base table can be viewed as representing an entity type, defined as the collection of all attributes of interest of entities with regard to that type. Multiple entity type tables are the cause of redundancies and update anomalies, which in turn are the subject of normalization:

Note very carefully that correct and complete mapping of a conceptual (business) model to a logical model will always yield fully normalized tables. It is only if and when poor design has bundled multiple entity types into single tables that those tables must undergo an explicit process of normalization. In other words, normalization is a redesign process that unbundles the entity types and eliminates the complications. The process involves decomposition without loss of information of the poorly designed tables via projection operations into multiple tables that each represents only one entity type and is, therefore, fully normalized.

The point here is, that normalization is inherently a redesign or verification issue. You will almost certainly encounter situations in which you have to redesign certain parts of a database (or you would like to andcannot). You can read about relational theory and normalization issues in a plethora of books and on the Internet. I will not go into much detail here and will only discuss some highlights.

Normalization addresses the issue of finding real entity types in relational designs. Declaring a table should ideally represent one entity type, but this cannot be assured just because a table was created. Normalization formalizes a set of rules, which should be applied when common sense fails.

Last updated: 2010-08-04