Normalization

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

This reduces duplication and prevents update anomalies.

Core Concepts

erDiagram
  CUSTOMER ||--o{ ORDER : places

  CUSTOMER {
    integer customer_id PK
    text name
    text email
  }

  ORDER {
    integer order_id PK
    integer customer_id FK
    real total
  }

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.