Database Normalization

Normalization is a process of certain rules to be applied while a database is being designed. The goal of this process is to prevent unnecesary data repeats and ensuring data dependencies and efficent use of data.

So what are these rules? There are five known normalization forms. Lets explain these one by one;

First Normal Form (1NF):
One row can contain only one information for a specific data.

Example;
WRONG: We have a Cars table and this table has a column called FuelType as nvarchar(255) and we are storing the data as

Cars
CarID	FuelType
1	"Petrol, Diesel"
2	"Diesel"
3	"Electric, Diesel"

CORRECT: We need to create a new table called FuelTypes which will store all the fuel types and instead of using FuelType nvarchar(255) column, FuelTypeID int should be used and needs to be related to FuelTypes table.

FuelTypes
FuelTypeID	Name
1	"Petrol"
2	"Diesel"
3	"Electric"

Cars
CarID	FuelTypeID
1	1
1	2
2	2
3	3
3	2

Second Normal Form (2NF)

A table that has a lookup column (which is primary key in other table) can not contain any information from that lookup table.

Example;
WRONG:

Services
ServiceID	WorkerID	WorkerName
1	2	Serkant
2	3	Catherine

Workers
WorkerID	WorkerName
2	Serkant
3	Catherine

CORRECT:

Services
ServiceID	WorkerID
1	2
2	3

Workers
WorkerID	WorkerName
2	Serkant
3	Catherine

Third Normal Form (3NF)

A table whose non-primary key fields are dependent only on the primary key and therefore have no dependence any other non-primary key field in the table.

Example;
WRONG:

Services
ServiceID	WorkerName	WorkerPhone
1	Serkant	0777 777 7777
2	Daniel	0777 888 8888

CORRECT:

Services
ServiceID	WorkerID
1	2
2	3

Workers
WorkerID	WorkerName	WorkerPhone
2	Serkant	0777 777 7777
3	Daniel	0777 888 8888

Fourth Normal Form (4NF)

A table can not contain two or more 1:n or n:m relationships that are not directly related.

Example;
WRONG:

Book Deliveries
Bookstore	Book	Area
Bookstore A	Book A	Area A
Bookstore A	Book A	Area B
Bookstore B	Book B	Area B
Bookstore C	Book B	Area C

CORRECT:

Bookstore Books
Bookstore	Book
Bookstore A	Book A
Bookstore B	Book A
Bookstore B	Book B

Bookstore Delivery Areas
Bookstore	Area
Bookstore A	Area A
Bookstore B	Area A
Bookstore B	Area B
Bookstore B	Area C

Fifth Normal Form (5NF)

This form deals with cases where information can be reconstructed from smaller pieces of information that can be maintained with less redundancy.

Example;
WRONG:

Salesman	Company	Product
Salesman A	Company A	Product A
Salesman A	Company A	Product B
Salesman B	Company B	Product A
Salesman B	Company B	Product B

CORRECT:

Company Salesmen
Company	Salesman
Company A	Salesman A
Company B	Salesman B

Company Products
Company	Product
Company A	Product A
Company A	Product B
Company B	Product A
Company B	Product B

Salesman Products
Salesman	Product
Salesman A	Product A
Salesman A	Product B
Salesman B	Product A
Salesman B	Product B