What Is Normalization?

What is normaliztion?

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

Table is in first normal form when each column has single value.

Consider 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 first normal form when every column in table has single value

Please have a look at column named color where you can find multiple values for the same column. In order to make this table in 1st 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

Table can be said to be in 2nd normal form when it satisfies condition/rules of first normal form. And every column in table should be dependant on primary key of table. Below is 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

Table is said to be in 3rd Normal form when it is in compliance with all the rules of second normal form.

And there should not be a column which is directly dependant on non-key field .

By looking at tblproduct, we can see 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 dependancies. 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 color of products.

ProductId

colorid

P001

1

P002

1

P002

2

P003

3

P003

1