intermediateDatabase Design100% Free

Database Normalization

Definition

Database normalization is the process of organizing a relational database to reduce data redundancy and improve data integrity by decomposing tables according to normal form rules.

Introduction to Database Normalization

Normalization is the art of designing relational databases that don't lie. A poorly normalized database allows contradictory data to exist (the same customer's address stored in two places with different values). Normal forms (1NF through BCNF) provide a systematic framework for eliminating these anomalies.

Syntax

SQL

-- Example: Denormalized orders table (bad)
CREATE TABLE orders_bad (
  order_id INT,
  customer_name VARCHAR(100),  -- redundant
  customer_email VARCHAR(100), -- redundant
  product_name VARCHAR(100),   -- redundant
  product_price DECIMAL(10,2)  -- redundant
);

-- Normalized (good)
CREATE TABLE customers (id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(100));
CREATE TABLE products (id INT PRIMARY KEY, name VARCHAR(100), price DECIMAL(10,2));
CREATE TABLE orders (id INT PRIMARY KEY, customer_id INT REFERENCES customers(id), product_id INT REFERENCES products(id));

Examples

First Normal Form (1NF)

-- VIOLATION: multi-valued column
CREATE TABLE students_bad (
  id INT,
  name VARCHAR(100),
  courses VARCHAR(500)  -- 'Math, Science, English' -- WRONG
);

-- 1NF COMPLIANT: atomic values
CREATE TABLE student_courses (
  student_id INT,
  course_name VARCHAR(100),
  PRIMARY KEY (student_id, course_name)
);

1NF requires atomic (indivisible) column values. Storing comma-separated lists violates 1NF. Create a separate table with one row per student-course combination.

Try in Playground

Third Normal Form (3NF)

-- VIOLATION: transitive dependency
CREATE TABLE employees_bad (
  emp_id INT,
  emp_name VARCHAR(100),
  dept_id INT,
  dept_name VARCHAR(100)  -- depends on dept_id, not emp_id -- WRONG
);

-- 3NF COMPLIANT: separate departments
CREATE TABLE employees (emp_id INT PRIMARY KEY, emp_name VARCHAR(100), dept_id INT);
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(100));

3NF requires that non-key columns depend only on the primary key, not on other non-key columns. dept_name depends on dept_id (not emp_id), so it belongs in a departments table.

Try in Playground

Common Mistakes

Over-normalizing — splitting tables too finely leads to excessive JOINs and poor read performance

Not indexing foreign keys after normalization

Confusing 2NF (no partial dependency on composite key) with 3NF (no transitive dependency)

Ignoring normalization entirely — denormalized databases develop update anomalies and inconsistencies

Frequently Asked Questions

What is database normalization?

Normalization is the process of organizing tables and columns to reduce redundancy and dependency. It follows a series of rules called Normal Forms (1NF, 2NF, 3NF, BCNF). Each normal form eliminates a specific type of data anomaly, resulting in cleaner, more consistent data that is easier to maintain.

What are the normal forms in database design?

1NF: Each column has atomic (single) values, no repeating groups. 2NF: 1NF + no partial dependencies on a composite primary key. 3NF: 2NF + no transitive dependencies (non-key columns depend only on the primary key). BCNF: A stricter version of 3NF where every determinant is a candidate key.

When should you denormalize a database?

Denormalization (deliberately violating normal forms) is used for performance optimization in read-heavy systems. It reduces the number of JOINs required for common queries. Data warehouses, analytics databases, and NoSQL systems often use denormalization. Always normalize first, then denormalize selectively where performance profiling shows it is needed.

What is the difference between 2NF and 3NF?

2NF eliminates partial dependencies — every non-key column must depend on the ENTIRE primary key (not just part of a composite key). 3NF eliminates transitive dependencies — non-key columns must depend directly on the primary key, not on other non-key columns. A table can be in 2NF but not 3NF.

Practice This in the SQL Playground

Write real queries, see live results, and master Database Normalization hands-on. 100% free.

Open SQL Playground Browse All Lessons