About

The Term “Data Normalization” came into vogue at about the same time that relational databases replaced earlier types of databases. What evolved were a series of standard Normal Forms, that can be arranged in a logical sequence. The major normal forms are First, Second , Third, Boyce-Codd, Fourth, and Fifth Normal Forms. These are abbreviated 1NF, 2NF, 3NF, BCNF, 4NF, and 5NF. BCNF was discovered after 4NF had already been named, but logically, it belongs between 3NF and 4NF.

1NF guarantees that any single value can be found in a table by providing a table name, a column name within the table, and enough data to specify a single row. Departures from 1NF can result in entire table scans instead of rapid indexed lookups. A table scan can run a thousand or even a million times slower than an indexed lookup.

Departures from 2NF through 5NF can result in certain anomalies when rows are inserted, deleted, or updated. These anomalies can result in a database that contradicts itself. Extremely careful programming is required to make sure this doesn’t occur. Most of the time, it’s better to adhere to the Normal Forms, thereby making self contradiction impossible.

However, there are ways to design databases that result in tables that are only partly normalized, and there are situations where these designs work better than a normalized design. One such way is called star schema design, which has its own tag. Deciding when to normalize calls for judgment. Some designers go through the work of normalizing their schemas, and then “denormalize” to get the design they want.

Recent database experts sometimes use the term “normalize” to refer to any form of table decomposition, with no implied reference to the standard normal forms.

And the term “normalization” is sometimes used in contexts other than relational database design. For example, a floating point number can be said to be "normalized" if both the exponent and the mantissa have been adjusted until it has been reduced to a standard canonical form.

Courtesy of SO Normalization tag.

history | show excerpt | excerpt history