Normalization
Normalization organizes database tables to reduce redundancy and prevent data anomalies. It splits data across related tables using foreign keys. Understanding 1NF, 2NF, and 3NF is essential for every database developer.
40 min•By Priygop Team•Last updated: Feb 2026
Normal Forms Explained
- 1NF: First Normal Form — Each cell contains a single atomic value. No arrays or lists in a cell. Each row is unique (has a primary key). Example: split 'tags: js,react,node' into a separate tags table
- 2NF: Second Normal Form — 1NF + every non-key column depends on the ENTIRE primary key. No partial dependencies. If composite key (order_id, product_id), price should depend on both, not just product_id
- 3NF: Third Normal Form — 2NF + no transitive dependencies. Non-key columns depend ONLY on the primary key. Example: if city determines zip_code, move zip_code to a separate cities table
- BCNF: Boyce-Codd Normal Form — Stricter 3NF. Every determinant is a candidate key. Rarely needed in practice
- Denormalization — Intentionally adding redundancy for performance. Store calculated values (total_orders on customer) to avoid expensive JOINs. Trade storage for speed
Schema Design Code
Example
-- Bad: Unnormalized table (violates 1NF and 3NF)
-- | order_id | customer_name | customer_email | items | total |
-- | 1 | Alice | alice@test.com | Widget,Gadget | 49.98 |
-- Good: Normalized schema (3NF)
CREATE TABLE customers (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE orders (
id INT PRIMARY KEY AUTO_INCREMENT,
customer_id INT NOT NULL,
status ENUM('pending','processing','shipped','delivered','cancelled') DEFAULT 'pending',
total DECIMAL(10,2) DEFAULT 0.00,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
CREATE TABLE products (
id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
price DECIMAL(10,2) NOT NULL,
stock INT DEFAULT 0,
category_id INT,
FOREIGN KEY (category_id) REFERENCES categories(id)
);
CREATE TABLE order_items (
id INT PRIMARY KEY AUTO_INCREMENT,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL DEFAULT 1,
price DECIMAL(10,2) NOT NULL, -- Price at time of purchase
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id)
);
-- Denormalization example: Add cached count to avoid COUNT queries
ALTER TABLE customers ADD COLUMN order_count INT DEFAULT 0;
-- Update with trigger or application code when orders change