SQL Normalization

Normalization is the process of organizing the tables in a relational database so that each fact is stored in exactly one place. A well-normalized schema removes redundant data, prevents inconsistent updates, and keeps your database trustworthy as it grows. It works by splitting one large, “everything in one table” design into smaller related tables connected by primary and foreign keys, following a progression of rules called normal forms — 1NF, 2NF, 3NF, and beyond.

Overview: How Normalization Works

Every table has functional dependencies between its columns. A column (or set of columns) X functionally determines column Y, written X -> Y, if knowing the value of X always tells you the value of Y. For example, in an orders table, order_id -> order_date, because each order has exactly one date. Normalization asks: does every non-key column depend on the whole primary key, and only the primary key? If not, the table is storing facts about something other than what its key identifies, and that mismatch is where redundancy and anomalies come from.

When a database is not normalized, the same fact (a customer’s email, a product’s name, a department’s name) gets copied into many rows. This causes three classic problems:

• Update anomaly — changing a fact in one row but not the duplicates leaves the database internally inconsistent.
• Insertion anomaly — you cannot record a new fact (e.g. a new department) until an unrelated fact (an employee in it) also exists.
• Deletion anomaly — deleting the last row referencing a fact accidentally deletes the fact itself.

Normalization fixes this through lossless decomposition: splitting a table into two or more tables such that joining them back together (via a foreign key) reproduces the exact original data, with no rows lost or invented. Each normal form is a stricter rule about which dependencies are allowed to remain in a single table.

Syntax: Building Normalized Tables

Normalization is a design process, not a single SQL statement, but it is expressed through CREATE TABLE statements that use a primary key in the “parent” table and a matching foreign key in the “child” table:

CREATE TABLE parent_table (
  parent_id INTEGER PRIMARY KEY,
  parent_attribute TEXT
);

CREATE TABLE child_table (
  child_id INTEGER PRIMARY KEY,
  parent_id INTEGER,
  child_attribute TEXT,
  FOREIGN KEY (parent_id) REFERENCES parent_table(parent_id)
);

Normal Form	Rule
1NF	Every column holds a single, atomic value — no comma-separated lists or repeating groups.
2NF	1NF, plus every non-key column depends on the entire primary key (no partial dependency on part of a composite key).
3NF	2NF, plus no non-key column depends on another non-key column (no transitive dependency).
BCNF	A stricter version of 3NF: every determinant (every column that determines another column) must itself be a candidate key.

Examples

Example 1 — First Normal Form (1NF)

This table stores an employee’s skills as a comma-separated list, which is not atomic and violates 1NF:

CREATE TABLE employees_unnormalized (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT,
  skills TEXT
);

INSERT INTO employees_unnormalized VALUES
  (1, 'Alice', 'SQL,Python,Java'),
  (2, 'Bob', 'SQL,C++');

SELECT * FROM employees_unnormalized;

Result:

employee_id	employee_name	skills
1	Alice	SQL,Python,Java
2	Bob	SQL,C++

Fixing this means giving each skill its own row in a separate table linked back by employee_id:

CREATE TABLE employees (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT
);

CREATE TABLE employee_skills (
  employee_id INTEGER,
  skill TEXT,
  PRIMARY KEY (employee_id, skill),
  FOREIGN KEY (employee_id) REFERENCES employees(employee_id)
);

INSERT INTO employees VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO employee_skills VALUES
  (1, 'SQL'), (1, 'Python'), (1, 'Java'),
  (2, 'SQL'), (2, 'C++');

SELECT e.employee_name, s.skill
FROM employees e
JOIN employee_skills s ON e.employee_id = s.employee_id
ORDER BY e.employee_name, s.skill;

Result:

employee_name	skill
Alice	Java
Alice	Python
Alice	SQL
Bob	C++
Bob	SQL

Now each skill can be queried, counted, or filtered exactly — no string parsing required.

Example 2 — Second Normal Form (2NF)

Here the primary key is composite (order_id, product_id), but product_name depends only on product_id — a partial dependency, which violates 2NF:

CREATE TABLE order_items_unnormalized (
  order_id INTEGER,
  product_id INTEGER,
  product_name TEXT,
  quantity INTEGER,
  PRIMARY KEY (order_id, product_id)
);

INSERT INTO order_items_unnormalized VALUES
  (1, 101, 'Keyboard', 2),
  (1, 102, 'Mouse', 1),
  (2, 101, 'Keyboard', 1);

The decomposed, 2NF-compliant version separates product facts from order-line facts:

CREATE TABLE products (
  product_id INTEGER PRIMARY KEY,
  product_name TEXT
);

CREATE TABLE order_items (
  order_id INTEGER,
  product_id INTEGER,
  quantity INTEGER,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (product_id) REFERENCES products(product_id)
);

INSERT INTO products VALUES (101, 'Keyboard'), (102, 'Mouse');
INSERT INTO order_items VALUES (1, 101, 2), (1, 102, 1), (2, 101, 1);

SELECT oi.order_id, p.product_name, oi.quantity
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
ORDER BY oi.order_id, p.product_name;

Result:

order_id	product_name	quantity
1	Keyboard	2
1	Mouse	1
2	Keyboard	1

If “Keyboard” is ever renamed, it now changes in exactly one row of products, instead of every order line that mentions it.

Example 3 — Third Normal Form (3NF)

This table has a transitive dependency: employee_id -> department_id -> department_name, meaning department_name depends on department_id, a non-key column, rather than directly on the primary key:

CREATE TABLE employees_transitive (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT,
  department_id INTEGER,
  department_name TEXT
);

INSERT INTO employees_transitive VALUES
  (1, 'Alice', 10, 'Engineering'),
  (2, 'Bob', 10, 'Engineering'),
  (3, 'Carol', 20, 'Marketing');

The 3NF fix moves department facts into their own table:

CREATE TABLE departments (
  department_id INTEGER PRIMARY KEY,
  department_name TEXT
);

CREATE TABLE employees_norm (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT,
  department_id INTEGER,
  FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO departments VALUES (10, 'Engineering'), (20, 'Marketing');
INSERT INTO employees_norm VALUES (1, 'Alice', 10), (2, 'Bob', 10), (3, 'Carol', 20);

SELECT e.employee_name, d.department_name
FROM employees_norm e
JOIN departments d ON e.department_id = d.department_id
ORDER BY e.employee_name;

Result:

employee_name	department_name
Alice	Engineering
Bob	Engineering
Carol	Marketing

How It Works Step by Step

Designing (or checking) a normalized schema follows a repeatable process:

• 1. List the functional dependencies. For every column, ask what it depends on. department_name depends on department_id; it does not depend on employee_id directly.
• 2. Check 1NF. Are all columns atomic? If a column stores a list or a repeating group, split it into a child table with one row per value.
• 3. Check 2NF. If the primary key is composite, does every non-key column depend on the whole key, or just part of it? Move partially-dependent columns into a table keyed by that part alone.
• 4. Check 3NF. Does any non-key column depend on another non-key column instead of the key? Move it into a table keyed by that determining column.
• 5. Verify the join is lossless. The decomposed tables must reconstruct the original data exactly via a natural join on the shared key — no rows should appear or disappear.

At query time, the engine’s planner benefits from this structure: foreign key columns are natural candidates for indexes, so joins between normalized tables can use fast index lookups instead of scanning duplicated text. Storage also shrinks, because a fact like a department name is stored once instead of once per employee — this matters for cache efficiency and I/O, not just tidiness.

Common Mistakes

Mistake 1: Leaving redundant data unnormalized

With customer details duplicated across every order, an update to one row leaves the others stale:

CREATE TABLE orders_denorm (
  order_id INTEGER PRIMARY KEY,
  customer_name TEXT,
  customer_email TEXT
);

INSERT INTO orders_denorm VALUES
  (1, 'Dana Lee', 'dana@example.com'),
  (2, 'Dana Lee', 'dana@example.com'),
  (3, 'Eli Wong', 'eli@example.com');

UPDATE orders_denorm SET customer_email = 'dana.lee@example.com' WHERE order_id = 1;

SELECT order_id, customer_name, customer_email FROM orders_denorm ORDER BY order_id;

Result:

order_id	customer_name	customer_email
1	Dana Lee	dana.lee@example.com
2	Dana Lee	dana@example.com
3	Eli Wong	eli@example.com

Dana Lee now has two different emails in the same table — a classic update anomaly. The fix is to move customer data into its own customers table and reference it by customer_id, so the email is stored exactly once.

Mistake 2: Filtering a comma-separated column instead of normalizing it

Storing multiple values in one column also breaks precise filtering. Using the unnormalized skills table from Example 1, a search for the “SQL” skill produces a false positive:

CREATE TABLE employees_unnormalized2 (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT,
  skills TEXT
);

INSERT INTO employees_unnormalized2 VALUES
  (1, 'Alice', 'SQL,Python,Java'),
  (2, 'Bob', 'SQL,C++'),
  (3, 'Carol', 'NoSQL,Go');

SELECT employee_name, skills FROM employees_unnormalized2 WHERE skills LIKE '%SQL%';

Result:

employee_name	skills
Alice	SQL,Python,Java
Bob	SQL,C++
Carol	NoSQL,Go

Carol matches even though her skill is “NoSQL”, not “SQL”, because LIKE '%SQL%' matches the substring. With a normalized employee_skills table, an exact-match query avoids this entirely:

CREATE TABLE employees3 (
  employee_id INTEGER PRIMARY KEY,
  employee_name TEXT
);

CREATE TABLE employee_skills3 (
  employee_id INTEGER,
  skill TEXT,
  PRIMARY KEY (employee_id, skill)
);

INSERT INTO employees3 VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Carol');
INSERT INTO employee_skills3 VALUES
  (1, 'SQL'), (1, 'Python'), (1, 'Java'),
  (2, 'SQL'), (2, 'C++'),
  (3, 'NoSQL'), (3, 'Go');

SELECT e.employee_name
FROM employees3 e
JOIN employee_skills3 s ON e.employee_id = s.employee_id
WHERE s.skill = 'SQL';

Result:

employee_name
Alice
Bob

Carol is correctly excluded, because her actual skill value is the exact string “NoSQL”, not “SQL”.

Best Practices

• Identify functional dependencies first — draw them out (X -> Y) before creating tables.
• Give every table a clear primary key that uniquely identifies what each row represents.
• Never store multiple values in a single column; use a child table with one row per value.
• Aim for 3NF as the default target for transactional (OLTP) schemas — it eliminates almost all anomalies while staying practical.
• Add foreign keys and index them — normalized schemas rely on joins, and joins need indexes to stay fast.
• Consider deliberate denormalization only for read-heavy reporting or analytics tables, and document why redundancy was reintroduced.
• Re-check normalization whenever you add a new column — a single new attribute can silently introduce a transitive dependency.

Practice Exercises

• Exercise 1: A table books(book_id, title, author_name, author_country) repeats author_country for every book by the same author. Which normal form does this violate, and how would you decompose it?
• Exercise 2: A table enrollments(student_id, course_id, student_email, grade) uses a composite key (student_id, course_id). student_email depends only on student_id. Write the CREATE TABLE statements for a 2NF-compliant decomposition.
• Exercise 3: Design and populate (with CREATE TABLE and INSERT) a small unnormalized table that violates 1NF, then write the normalized version and a JOIN query that reconstructs the original information.

Summary

• Normalization organizes data so each fact lives in exactly one place, guided by functional dependencies.
• 1NF requires atomic column values — no lists or repeating groups.
• 2NF requires every non-key column to depend on the whole primary key, not just part of a composite key.
• 3NF requires every non-key column to depend directly on the key, not on another non-key column (no transitive dependencies).
• Unnormalized data causes update, insertion, and deletion anomalies; normalized data avoids them through lossless decomposition and foreign keys.
• Normalized schemas trade some query-time joins for storage savings, consistency, and index-friendly access — a trade worth making for most transactional databases.