DEV Community

Sandeep Borhade
Sandeep Borhade

Posted on

Database Table Normalization

By using normalization, we can organize database to reduce redundancy and improve data integrity.

Business Scenario: Customer Orders

Unnormalized Table

Suppose you have an unnormalized table that stores customer orders along with their order details:

CREATE TABLE CustomerOrders (
    OrderID INT,
    CustomerName VARCHAR(100),
    OrderDate DATE,
    ProductIDs VARCHAR(100),
    ProductNames VARCHAR(100),
    Quantities VARCHAR(100)
);

-- Sample data
INSERT INTO CustomerOrders (OrderID, CustomerName, OrderDate, ProductIDs, ProductNames, Quantities)
VALUES (1, 'Alice', '2025-01-01', '101,102', 'Laptop,Mouse', '1,2');
Enter fullscreen mode Exit fullscreen mode

In this example, the ProductIDs, ProductNames, and Quantities columns contain multiple values separated by commas, which violates the 1NF rule.

First Normal Form (1NF)

To achieve the First Normal Form (1NF), ensure that each column contains atomic values and there are no repeating groups. We need to separate the order details into individual rows:

CustomerOrders Table:

CREATE TABLE CustomerOrders (
    OrderID INT,
    CustomerName VARCHAR(100),
    OrderDate DATE
);

-- Sample data
INSERT INTO CustomerOrders (OrderID, CustomerName, OrderDate)
VALUES (1, 'Alice', '2025-01-01');
Enter fullscreen mode Exit fullscreen mode

OrderDetails Table:

CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    ProductName VARCHAR(100),
    Quantity INT,
    FOREIGN KEY (OrderID) REFERENCES CustomerOrders(OrderID)
);

-- Sample data
INSERT INTO OrderDetails (OrderDetailID, OrderID, ProductID, ProductName, Quantity)
VALUES 
    (1, 1, 101, 'Laptop', 1),
    (2, 1, 102, 'Mouse', 2);
Enter fullscreen mode Exit fullscreen mode

Second Normal Form (2NF)

To achieve the Second Normal Form (2NF), remove partial dependencies. This means that non-key attributes should depend on the whole primary key. We need to separate the customer and order details into two tables:

CustomerOrders Table:

CREATE TABLE CustomerOrders (
    OrderID INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    OrderDate DATE
);

-- Sample data
INSERT INTO CustomerOrders (OrderID, CustomerName, OrderDate)
VALUES 
    (1, 'Alice', '2025-01-01'),
    (2, 'Bob', '2025-01-02');
Enter fullscreen mode Exit fullscreen mode

OrderDetails Table:

CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    ProductName VARCHAR(100),
    Quantity INT,
    FOREIGN KEY (OrderID) REFERENCES CustomerOrders(OrderID)
);

-- Sample data
INSERT INTO OrderDetails (OrderDetailID, OrderID, ProductID, ProductName, Quantity)
VALUES 
    (1, 1, 101, 'Laptop', 1),
    (2, 1, 102, 'Mouse', 2),
    (3, 2, 103, 'Keyboard', 1);
Enter fullscreen mode Exit fullscreen mode

Third Normal Form (3NF)

To achieve the Third Normal Form (3NF), remove transitive dependencies. This means that non-key attributes should not depend on other non-key attributes. We need to separate the product details into another table:

Products Table:

CREATE TABLE Products (
    ProductID INT PRIMARY KEY,
    ProductName VARCHAR(100)
);

-- Sample data
INSERT INTO Products (ProductID, ProductName)
VALUES 
    (101, 'Laptop'),
    (102, 'Mouse'),
    (103, 'Keyboard');
Enter fullscreen mode Exit fullscreen mode

OrderDetails Table (Updated):

CREATE TABLE OrderDetails (
    OrderDetailID INT PRIMARY KEY,
    OrderID INT,
    ProductID INT,
    Quantity INT,
    FOREIGN KEY (OrderID) REFERENCES CustomerOrders(OrderID),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Sample data
INSERT INTO OrderDetails (OrderDetailID, OrderID, ProductID, Quantity)
VALUES 
    (1, 1, 101, 1),
    (2, 1, 102, 2),
    (3, 2, 103, 1);
Enter fullscreen mode Exit fullscreen mode

Boyce-Codd Normal Form (BCNF)

BCNF is a stricter version of 3NF. To achieve BCNF, ensure that for every functional dependency (A -> B), A is a superkey. The tables above are already in BCNF.

Fourth Normal Form (4NF)

To achieve the Fourth Normal Form (4NF), remove multi-valued dependencies. This means that a record should not contain two or more independent multi-valued facts about an entity. Suppose we have a table that stores employee skills and projects:

EmployeeSkillsProjects Table:

CREATE TABLE EmployeeSkillsProjects (
    EmployeeID INT,
    Skill VARCHAR(100),
    Project VARCHAR(100)
);

-- Sample data
INSERT INTO EmployeeSkillsProjects (EmployeeID, Skill, Project)
VALUES 
    (1, 'C#', 'ProjectA'),
    (1, 'SQL', 'ProjectB'),
    (2, 'Java', 'ProjectA');
Enter fullscreen mode Exit fullscreen mode

To achieve 4NF, we separate the skills and projects into two tables:

EmployeeSkills Table:

CREATE TABLE EmployeeSkills (
    EmployeeID INT,
    Skill VARCHAR(100)
);

-- Sample data
INSERT INTO EmployeeSkills (EmployeeID, Skill)
VALUES 
    (1, 'C#'),
    (1, 'SQL'),
    (2, 'Java');
Enter fullscreen mode Exit fullscreen mode

EmployeeProjects Table:

CREATE TABLE EmployeeProjects (
    EmployeeID INT,
    Project VARCHAR(100)
);

-- Sample data
INSERT INTO EmployeeProjects (EmployeeID, Project)
VALUES 
    (1, 'ProjectA'),
    (1, 'ProjectB'),
    (2, 'ProjectA');
Enter fullscreen mode Exit fullscreen mode

Fifth Normal Form (5NF)

To achieve the Fifth Normal Form (5NF), ensure that the table is free from join dependencies. This means that the table should not contain any non-trivial join dependencies. The tables above are already in 5NF.

Image of Timescale

Timescale – the developer's data platform for modern apps, built on PostgreSQL

Timescale Cloud is PostgreSQL optimized for speed, scale, and performance. Over 3 million IoT, AI, crypto, and dev tool apps are powered by Timescale. Try it free today! No credit card required.

Try free

Top comments (0)

A Workflow Copilot. Tailored to You.

Pieces.app image

Our desktop app, with its intelligent copilot, streamlines coding by generating snippets, extracting code from screenshots, and accelerating problem-solving.

Read the docs

👋 Kindness is contagious

Dive into an ocean of knowledge with this thought-provoking post, revered deeply within the supportive DEV Community. Developers of all levels are welcome to join and enhance our collective intelligence.

Saying a simple "thank you" can brighten someone's day. Share your gratitude in the comments below!

On DEV, sharing ideas eases our path and fortifies our community connections. Found this helpful? Sending a quick thanks to the author can be profoundly valued.

Okay