RDBMS Normalization

Normalization in RDBMS

When designing a database, one of the main goals is to store data in a clean, organized, and efficient way. If the same information is stored repeatedly in multiple places, it can lead to wasted storage space and data inconsistencies.

Normalization helps solve these problems. It is the process of organizing data into smaller, related tables to reduce duplication and improve data consistency.

For example, instead of storing a customer's address in every order record, the customer's details are stored in one table, and the orders are stored in another. These tables are then connected using keys.

Normalization is an important concept in relational database design because it makes databases easier to manage, update, and maintain.

Why Do We Need Normalization?

Imagine an online shopping database where customer information is stored with every order.

Rahul's name and city are repeated in every row. This creates several problems:

• Wasted storage space.
• Repeated information.
• Higher chance of mistakes.
• Difficult updates to customer details.

If Rahul moves to another city, every row must be updated. Missing even one row would leave incorrect data in the database.

Database After Normalization

Customers Table

Orders Table

Now the customer information is stored only once. The CustomerID connects both tables. This design is easier to maintain and reduces duplicate data.

Benefits of Normalization

Normalization provides many advantages:

• Reduces duplicate data.
• Saves storage space.
• Improves data accuracy.
• Makes updates easier.
• Prevents inconsistent information.
• Improves database organization.
• Simplifies maintenance.
• Supports better database performance in many situations.

Normal Forms

Normalization is performed in several stages called Normal Forms. Each normal form removes a specific type of data problem.

First Normal Form (1NF)

A table is in First Normal Form if every column contains only one value, there are no repeating groups, and each row is unique.

Incorrect example:

The Subjects column contains multiple values. A better design stores one subject per row.

Second Normal Form (2NF)

A table is in Second Normal Form if it already satisfies 1NF and every non-key column depends on the entire primary key.

This usually means separating information that does not belong together into different tables. For example, student details and course details should not be stored in the same table if they describe different entities.

Third Normal Form (3NF)

A table is in Third Normal Form if it already satisfies 2NF, non-key columns depend only on the primary key, and there are no unnecessary dependencies between non-key columns.

For example, instead of storing both city and state repeatedly with every customer, related information can be placed in separate tables when appropriate.

Third Normal Form is commonly used in most database applications because it provides a good balance between organization and performance.

SQL Example

Normalization is a database design process rather than an SQL command, but SQL is used to create the normalized tables.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    Name VARCHAR(100),
    City VARCHAR(50)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    Product VARCHAR(100),
    FOREIGN KEY (CustomerID)
        REFERENCES Customers(CustomerID)
);

Here, customer information is stored in one table, order information is stored in another table, and the CustomerID foreign key connects both tables.

Best Practices

When designing a normalized database:

• Store each type of information in its own table.
• Use primary keys to uniquely identify records.
• Use foreign keys to create relationships.
• Avoid storing the same information in multiple places.
• Apply at least the first three normal forms for most applications.