Third Normal Form (3NF) in SQL

Introduction

Third Normal Form helps organize data in a database so it is simple and avoids repeating information.

When you want to make sure each piece of data is stored only once.

When you want to avoid mistakes caused by duplicated data.

When you want to make updating data easier and faster.

When you want your database to be clear and easy to understand.

When you want to reduce wasted space in your database.

Syntax

SQL

-- There is no direct SQL command for 3NF.
-- It is a design rule to follow when creating tables.
-- To achieve 3NF:
-- 1. Make sure the table is in Second Normal Form (2NF).
-- 2. Remove columns that depend on other non-key columns.
-- 3. Create new tables for those columns.
-- 4. Link tables using keys.

3NF is about table design, not a single SQL command.

It means no column should depend on another non-key column.

Examples

This table repeats customer and product info for every order, which is not 3NF.

SQL

-- Example: A table not in 3NF
CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerName VARCHAR(100),
  CustomerAddress VARCHAR(200),
  ProductID INT,
  ProductName VARCHAR(100)
);

Now customer and product info is stored once, and Orders links to them by ID.

SQL

-- To fix, split into three tables:
CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  CustomerName VARCHAR(100),
  CustomerAddress VARCHAR(200)
);

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

CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  ProductID INT,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
  FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

Sample Program

This example shows tables designed in 3NF and a query joining them to get full order details.

SQL

-- Create Customers table
CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  CustomerName VARCHAR(100),
  CustomerAddress VARCHAR(200)
);

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

-- Create Orders table linking Customers and Products
CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  CustomerID INT,
  ProductID INT,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID),
  FOREIGN KEY (ProductID) REFERENCES Products(ProductID)
);

-- Insert sample data
INSERT INTO Customers VALUES (1, 'Alice', '123 Apple St');
INSERT INTO Products VALUES (10, 'Book');
INSERT INTO Orders VALUES (100, 1, 10);

-- Query to show order with customer and product info
SELECT o.OrderID, c.CustomerName, c.CustomerAddress, p.ProductName
FROM Orders o
JOIN Customers c ON o.CustomerID = c.CustomerID
JOIN Products p ON o.ProductID = p.ProductID;

Important Notes

3NF helps keep data clean and easy to update.

Always check for columns that depend on other non-key columns.

Use foreign keys to connect tables after splitting data.

Summary

Third Normal Form means no data depends on non-key columns.

It reduces duplicate data by splitting tables.

It makes databases easier to maintain and less error-prone.