Normalization

Normalization organizes data so each fact is stored once in the right table.

This reduces duplication and prevents update anomalies.

Core Concepts

Normalization level	Practical goal
1NF	Atomic columns (no lists in one field)
2NF	Non-key columns depend on whole key
3NF	Remove transitive dependency/redundancy

Code Examples

-- Less normalized example (duplicates customer name repeatedly).
CREATE TABLE orders_raw_norm (
  order_id        INTEGER PRIMARY KEY,
  customer_name   TEXT NOT NULL,
  customer_email  TEXT NOT NULL,
  total           REAL NOT NULL
);

Expected output
Single table created with duplicated customer attributes risk.

-- Normalized design: separate customer entity and order facts.
CREATE TABLE customers_norm (
  customer_id INTEGER PRIMARY KEY,
  name        TEXT NOT NULL,
  email       TEXT NOT NULL UNIQUE
);

CREATE TABLE orders_norm (
  order_id     INTEGER PRIMARY KEY,
  customer_id  INTEGER NOT NULL,
  total        REAL NOT NULL,
  FOREIGN KEY (customer_id) REFERENCES customers_norm(customer_id)
);

Expected output
Two related tables created with reduced redundancy.

-- Join query to recompose normalized data.
SELECT o.order_id, c.name, c.email, o.total
FROM orders_norm AS o
INNER JOIN customers_norm AS c
  ON o.customer_id = c.customer_id
ORDER BY o.order_id;

order_id	name	email	total
Rows appear after inserting normalized sample data

SQLite-Specific Nuances

SQLite Nuance

SQLite's lightweight engine benefits from clean normalized schemas because they simplify constraints and reduce accidental inconsistency.

Common Pitfalls / Best Practices

Pitfall

Overloading one table with mixed concerns (customer + order + shipment details), then struggling with updates.

Best Practice

Normalize by default; denormalize later only when measured performance needs justify it.

Quick Challenge

Split this concept into two tables: students(student_id, name) and grades(grade_id, student_id, score).

View Solution

CREATE TABLE students (
  student_id INTEGER PRIMARY KEY,
  name       TEXT NOT NULL
);

CREATE TABLE grades (
  grade_id    INTEGER PRIMARY KEY,
  student_id  INTEGER NOT NULL,
  score       REAL NOT NULL,
  FOREIGN KEY (student_id) REFERENCES students(student_id)
);

Expected output
Student identity and grade facts are separated cleanly.