For an instructor lead, in-depth look at learning SQL click below.
In the world of Structured Query Language (SQL), two concepts that tend to appear quite often are ‘normalization’ and ‘denormalization’. These are database design techniques that deal with how we store data in a relational database. Let’s delve into what each term represents and also display some examples of SQL code to illustrate their use.
Normalization
Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller ones and linking them using relationships. The main aim of Normalization is to add, delete or modify fields that can be made in a single table. This minimizes the chances of data anomalies.
Three main types of normalization are usually discussed:
First Normal Form (1NF):
This form entails the removal of any duplicate columns from the same table and creation of separate tables for related data.
|
1 2 3 4 5 6 7 8 9 10 11 |
CREATE TABLE Customers ( CustomerID int NOT NULL, CustomerName varchar(255), ContactName varchar(255), Address varchar(255), City varchar(255), PostalCode varchar(255), Country varchar(255), ); |
Second Normal Form (2NF):
The table must be in 1NF and all non-key attributes must be fully dependent on the primary key.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Orders ( OrderID int NOT NULL, CustomerID int, EmployeeID int, OrderDate date NOT NULL ); |
Third Normal Form (3NF):
The table must be in 2NF and there should be no transitive dependent for any non-prime attribute.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Products ( ProductID int NOT NULL, ProductName varchar(255) NOT NULL, SupplierID int, CategoryID int, ); |
Denormalization
Denormalization, on the other hand, is the process of trying to improve the read performance of a database at the expense of losing some write performance by adding redundant copies of data. It is usually conducted after normalization process (1NF, 2NF, 3NF). Denormalization helps to boost your query performance and simplifies data retrieval.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE Orders ( OrderID int NOT NULL, CustomerName varchar(255), ProductName varchar(255), OrderDate date NOT NULL ); |
To sum up, the choice on whether to normalize or denormalize your data would depend on what your application prioritizes: if it is data integrity and lack of redundancy, normalization is the way to go. If read speed is a major concern, then denormalization may be a better option. The trick is achieving the right balance according to your requirements.