Database normalization

Database normalization is the process of organizing the data to minimize redundancy and dependency. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database via the defined relationships(Source:Wikipedia)

 

Why do I need normalization while storing the data: normalization helps the data to be queried and manipulated. If we do not normalize then the following could be the issues:

·         The same information might be represented in multiple rows. When we update, insert or delete  the existing data or add new data it might result in problems.

·         To minimize the redesign when extending the existing data structure.

·         To make the data model more informative to the users

 

There are primarily 3 types of normalization that are relevant to the end users:

First Normal Form (1NF)

First normal form (1NF) sets the very basic rules for an organized database:

·         Eliminate duplicative columns from the same table.

·         Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

For more details, read Putting your Database in First Normal Form

Second Normal Form (2NF)

Second normal form (2NF) further addresses the concept of removing duplicative data:

·         Meet all the requirements of the first normal form.

·         Remove subsets of data that apply to multiple rows of a table and place them in separate tables.

·         Create relationships between these new tables and their predecessors through the use of foreign keys.

For more details, read Putting your Database in Second Normal Form

Third Normal Form (3NF)

Third normal form (3NF) goes one large step further:

·         Meet all the requirements of the second normal form.

·         Remove columns that are not dependent upon the primary key.

For more details, read Putting your Database in Third Normal Form

 

The  benefits of normalization are as follows:

1.) Data gets compressed(Data compression due to reduced redundancies in the normalization layer) hence lesser number of pages need to be scanned for getting the required information.

 

 

2.) The audit process becomes easier and multiple locations could be loaded onto the data warehouse  due lower ETL times.

 

 

 The process of normalization helps at data transformation, data consolidation and thereafter Business Intelligence reporting.