Monday, March 30, 2009

The Normal Forms: In a Nutshell

The Normal Forms: In a Nutshell

In this series, I have tried to explain in non-mathematical terms what the first three Normal Forms mean and how they determine database design.

This is not the most useful method of learning normalization. In fact, many expert developers never learn the formal definition of the normal forms. If you haven't already, I suggest you read the following series:

Normalization
ER Diagramming

However, I think it is useful to know what the Normal Forms are. Sometimes when you get stuck in a design, you can go back to the definitions to get yourself out of trouble.

So, in summary:

First Normal Form (1NF) says that each record must be unique, that is, it has a primary key. There are some additional restrictions on how such uniqueness is maintained such as not allowing positional referencing and no repeated columns.

Second Normal Form (2NF) says that each field in the record must depend on the whole primary key, not just a part of it.

Third Normal Form (3NF) says that no field must depend on any other field except the primary key.

William Kent, author of A Simple Guide to Five Normal Forms in Relational Database Theory, once abbreviated the first three normal forms like this:

"The Key, the whole Key, and nothing but the Key, so help me Codd."
.

2 comments:

Anonymous said...

great tutorial,it was enlightening..thank you Roger hoping for further explanation on bcnf,5thnf and dknf

Ian Bond said...

Thank you, this is the best explanation of Normalization I've seen in 30 plus years as an IT professional.
The examples provided really help to explain why getting normalization right is so important, and demonstrate a pragmatic approach.