What is normalization?
Normalization is the process of arranging and managing data. There are basically three forms which are quite sufficient to arrange and manage data.
Below are the 3 normal forms,
1st Normal Form
The table is in the first normal form when each column has a single value.
Consider the below table
Tblproduct
|
Productid
|
Name
|
Color
|
Supplier
|
mobilenumber
|
qty
|
Per unit price
|
price
|
|
P001
|
Prod1
|
Red
|
Supplier1
|
123456789
|
1
|
10
|
10
|
|
P002
|
Prod2
|
Red,Blue
|
Supplier2
|
986789123
|
1
|
10
|
10
|
|
P003
|
Prod3
|
Red,green
|
Supplier1
|
123456789
|
1
|
10
|
10
|
tblproduct will be considered in the first normal form when every column in the table has a single value.
Please have a look at the column named color where you can find multiple values for the same column. In order to make this table in 1
st normal form, we have to separate that column value as below.
Tblproduct
|
Productid
|
Name
|
Color
|
Supplier
|
qty
|
Per unit price
|
price
|
mobilenumber
|
|
P001
|
Prod1
|
Red
|
Supplier1
|
1
|
10
|
10
|
123456789
|
|
P002
|
Prod2
|
Red
|
Supplier2
|
1
|
10
|
10
|
986789123
|
|
P002
|
Prod2
|
Blue
|
Supplier2
|
1
|
10
|
10
|
986789123
|
|
P003
|
Prod3
|
Red
|
Supplier1
|
2
|
10
|
20
|
123456789
|
|
P003
|
Prod3
|
Green
|
Supplier1
|
3
|
10
|
30
|
123456789
|
Now tblproduct is in first normal form.
Second Normal form
The table can be said to be in 2nd normal form when it satisfies condition/rules of first normal form. And every column in the table should be dependant on the primary key of the table. Below is the second normal form of tblproduct.
Now we are going to divide tblproduct into 3 different entities
tblproduct
|
Productid
|
Name
|
Supplierid
|
Qty
|
Per unit price
|
price
|
mobilenumber
|
|
P001
|
Prod1
|
1
|
1
|
10
|
10
|
123456789
|
|
P002
|
Prod2
|
2
|
1
|
10
|
10
|
986789123
|
|
P003
|
Prod3
|
1
|
1
|
10
|
10
|
123456789
|
tblcolor
|
Colorid
|
Name
|
|
1
|
Red
|
|
2
|
Blue
|
|
3
|
Green
|
tblsupplier
|
Supplierid
|
Name
|
|
1
|
Supplier1
|
|
2
|
Supplier1
|
Below is a mapping table for color of products.
tblproductcolormapping
|
ProductId
|
Colorid
|
|
P001
|
1
|
|
P002
|
1
|
|
P002
|
2
|
|
P003
|
3
|
|
P003
|
1
|
Third Normal Form
The table is said to be in 3rd Normal form when it is in compliance with all the rules of the second normal form.
And there should not be a column which is directly dependant on the non-key field.
By looking at tblproduct, we can see the mobile number is not related to product and price is also dependant on qty and per-unit price column so we need to remove these dependencies. Price is calculative column so we can calculate it on the fly by multiplying qty and per-unit price. The structure of tables will be like below if tblproduct is in 3rd normal form.
|
Productid
|
Name
|
Supplierid
|
qty
|
Per unit price
|
|
P001
|
Prod1
|
1
|
1
|
10
|
|
P002
|
Prod2
|
2
|
1
|
10
|
|
P003
|
Prod3
|
1
|
1
|
10
|
|
Colorid
|
Name
|
|
1
|
Red
|
|
2
|
Blue
|
|
3
|
Green
|
|
Supplierid
|
Name
|
mobilenumber
|
|
1
|
Supplier1
|
123456789
|
|
2
|
Supplier1
|
986789123
|
Below is a mapping table for the color of products.
|
ProductId
|
colorid
|
|
P001
|
1
|
|
P002
|
1
|
|
P002
|
2
|
|
P003
|
3
|
|
P003
|
1
|