Normalization In normalized database, the database has more number of tables. As normalization means creating more tables from lesser ones. As the...

Normalization

In normalized database, the database has more number of tables. As normalization means creating more tables from lesser ones. As the numbers of tables created are higher, the more joints will be needed to get at the data. Normalization normally removes the duplicity i.e. redundant data which results in increase in database performance.

Impacts of Normalization on Database Performance

Normalization has various impacts on database performance. These are:

1. Removal of Redundancy: Normalization increases performance by removing the redundancy.

2. Less Storage Space: Normalization results into less storage space, because the redundant data is removed.

3. Reliability: It helps in producing the reliable information.

4. Efficient System: In order to provide efficient system, Normalization plays a very important role.

In spite of increasing performance, Normalization requires much access time to execute the queries. Moreover, it also needs more number of joints.

Example:

We have two tables i.e. orders and customers

1. Orders table:

• Select * from orders;

1. Customers table:

• Select * from customers;

• select * from db.orders O INNER JOIN db.customers C ON O.Customer_ID= C.Customer_ID;

Denormalization

Denormalization is generally a process of optimizing the read performance or in order to reduce the access time, the Denormalization is done.

Impacts of Denormalization on Database Performance

Denormalization can also improve performance in various ways:

1. Improve Reading Data Performance: Denormalization results into much storage space, but the desire view of the data is readily available.

2. Removal of Joints: As the number of joints needs to be created while normalization are more. But Denormalization results into removal of these joints.

3. Accessibility: The time taken to execute the query is less. Thus, Denormalization results into less access time.

4. Maintain Information: The Denormalization helps in maintaining the history information.

Hence, in spite of various benefits, Normalization results into more access time because more numbers of joints are needed.

Therefore, developers might slightly denormalize the design for performance reasons.

Sample Database

Database name: sample

Tables: models, Models_networks, Networks, Countries_networks and Countries

Models:

• Select * from models;

(It takes 0.0004sec to execute this query).

Models_networks:

• Select * from models_networks;

(It takes 0.0004sec to execute this query).

Networks:

• Select * from networks;

(It takes 0.0004sec to execute this query).

Countries_networks:

• Select * from countries_networks;

(It takes 0.0004sec to execute this query).

Countries:

• Select * from countries;

(It takes 0.0004sec to execute this query).

• Select

m.name AS model_name,

c.countryName,

COUNT(*) AS network_id

FROM

models AS m

INNER JOIN models_networks AS mn ON mn.model_id = m.id

INNER JOIN networks AS n ON n.id = mn.network_id

INNER JOIN countries_networks AS cn ON cn.network_id = n.id

INNER JOIN countries AS c ON c.id = cn.country_id

WHERE

c.countryName = country1

GROUP BY

m.name,

c.countryName

(It takes 0.0007sec to execute this query).

Hence, in spite of various benefits, Normalization results into more access time because more numbers of joints are needed.

Therefore, developers might slightly denormalize the design for performance reasons.

REFERENCES & CITATIONS

• (http://sqlmag.com/database-performance-tuning/sql-design-why-you-need-database-normalization)

• (http://stackoverflow.com/questions/1379340/what-is-the-resource-impact-from-normalizing-a-database)

• (http://databases.about.com/od/specificproducts/a/Should-I-Normalize-My-Database.htm)