Home About Us SQL Interview Book Contact Us RSS
Articles
Tools
Code Snippets
Links
Tips & Tricks
FAQ
Resources
Articles
Code Snippets
Links
FAQ
Resources

Saravana Kumar
Vinod Kumar


Normalizing Techniques - A closer look

More often than not database users have not able to  understand what the various normalization techniques are. In this article I would try to address these principles in a simple terms and examples. Applying and knowing the principles of normalize and implement the them to your daily database design is an simple process and this can come with performance improvements. Not wasting much time lets get going ...

What is Normalization?

Normalization is a process of efficiently organizing your data in your database. The goals in doing so: Eliminate all the redundant data and ensure data dependencies. Both of these goals are worth achieving. Normalizing tables would reduce the amount of space a database may consume.

And as said earlier each of these are a set of rules. Each of these rules are called as "Normal Form" or NF in short. All the rules are cummulative in nature. Meaning if we have three of the rules adhered then we are in the 3NF. In this document we will

First Normal Form ( 1NF )

  • Eliminate repeating groups from the same table
  • Aggregate similiar data in separate tables and identify each row with an unique identifier

In simple language if we were to say each attribute of the relation would be atomic in nature for 1NF. Look at the example below to understand better.

Second Normal Form ( 2NF )

Moving forward lets take a look at the rules that goven 2NF. We get a step even more closer to remove duplicate records.

  • Remove data that apply to multiple rows and place them in a separate table
  • Relate the above table with foreign keys

Consider the below example to understand the same.

Third Normal Form ( 3NF )

This is the most preferred normalization technique followed for most of the database.

  • Eliminate all fields that donot depend on the Primary key

Values in a record that are not part of that record's key do not belong in the table. In general, any time the contents of a group of fields may apply to more than a single record in the table, consider placing those fields in a separate table.

Note: All these normalization are cummulative in nature. I re-iterate this point.

There are 4NF otherwise called as Boyce-Codd normal form (BCNF). I wouldnot deal much into this form as it becomes far beyond practicle limits to have such a requirement. The rule is, we are in BCNF if and only if every determinant is a candidate key.