MySQLNormalization

Database Normalization

Normalization is the process of organizing a relational database to reduce data redundancy and eliminate update anomalies. It works by decomposing large, flat tables into smaller, focused tables linked by foreign keys. The normal forms — 1NF through BCNF — are a progressive series of rules, each building on the previous.

Why Normalize?

A poorly designed table causes three types of anomalies:

Insertion anomaly: You cannot store new data without also storing unrelated data. In an "Orders" table that includes customer address columns, you cannot record a customer's address until they place their first order.

Update anomaly: A single logical change requires updating many rows. If a customer changes their address, you must update every row in the orders table for that customer.

Deletion anomaly: Deleting one piece of data destroys unrelated data. Deleting a customer's only order loses their contact information.

Normalization eliminates these anomalies by ensuring each fact is stored in exactly one place.

The Unnormalized Starting Table

Suppose we start with a single flat table imported from a spreadsheet:

SQL
-- Messy, unnormalized table
CREATE TABLE orders_flat (
  order_id       INT,
  order_date     DATE,
  customer_id    INT,
  customer_name  VARCHAR(100),
  customer_email VARCHAR(255),
  customer_city  VARCHAR(100),
  product_id1    INT,
  product_name1  VARCHAR(100),
  qty1           INT,
  price1         DECIMAL(10,2),
  product_id2    INT,
  product_name2  VARCHAR(100),
  qty2           INT,
  price2         DECIMAL(10,2),
  product_id3    INT,
  product_name3  VARCHAR(100),
  qty3           INT,
  price3         DECIMAL(10,2)
);

This table has multiple problems:

  • Product data is repeated in up to 3 sets of columns (product_id1/2/3...).
  • Customer name/email/city is duplicated for every order that customer makes.
  • An order with 4 items cannot be stored at all.
First Normal Form (1NF)

A table is in 1NF when:

  1. Every column contains atomic (indivisible) values — no arrays or sets in a single cell.
  2. There are no repeating groups — no series of similar columns (product1, product2, product3).
  3. Each row is uniquely identifiable (has a primary key).

SQL
-- 1NF: Remove repeating product columns; each order-product pair becomes a row
CREATE TABLE orders_1nf (
  order_id      INT,
  order_date    DATE,
  customer_id   INT,
  customer_name VARCHAR(100),
  customer_email VARCHAR(255),
  customer_city VARCHAR(100),
  product_id    INT,
  product_name  VARCHAR(100),
  qty           INT,
  price         DECIMAL(10,2),
  PRIMARY KEY (order_id, product_id)  -- composite PK
);

-- Now a 3-item order is 3 rows:
-- (1, '2024-07-01', 42, 'Alice', 'alice@...', 'Toronto', 10, 'Widget', 2, 9.99)
-- (1, '2024-07-01', 42, 'Alice', 'alice@...', 'Toronto', 20, 'Gadget', 1, 24.99)
-- (1, '2024-07-01', 42, 'Alice', 'alice@...', 'Toronto', 30, 'Thingo', 3, 4.99)
Note
After 1NF, customer data is still duplicated across rows. That redundancy is addressed in 2NF.
Second Normal Form (2NF)

A table is in 2NF when:

  1. It is already in 1NF.
  2. Every non-key column is fully functionally dependent on the entire primary key — not just part of it.

2NF is only relevant for tables with a composite primary key. In our 1NF table, the PK is (order_id, product_id). Customer details (name, email, city) depend only on order_id, not on the full composite PK. That is a partial dependency — violating 2NF.

SQL
-- 2NF: Move partial dependencies to their own tables

-- Customer data depends only on customer_id
CREATE TABLE customers (
  id    INT          NOT NULL AUTO_INCREMENT,
  name  VARCHAR(100) NOT NULL,
  email VARCHAR(255) NOT NULL,
  city  VARCHAR(100),
  PRIMARY KEY (id)
);

-- Order data depends only on order_id
CREATE TABLE orders (
  id          INT  NOT NULL AUTO_INCREMENT,
  customer_id INT  NOT NULL,
  order_date  DATE NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (customer_id) REFERENCES customers (id)
);

-- Order items depend on the full composite key (order_id + product_id)
CREATE TABLE order_items (
  order_id   INT            NOT NULL,
  product_id INT            NOT NULL,
  qty        INT            NOT NULL,
  price      DECIMAL(10,2)  NOT NULL,
  PRIMARY KEY (order_id, product_id),
  FOREIGN KEY (order_id) REFERENCES orders (id)
);
Third Normal Form (3NF)

A table is in 3NF when:

  1. It is already in 2NF.
  2. There are no transitive dependencies — non-key columns depend on other non-key columns.

Example: suppose orders had a city column and also a timezone column, where timezone is determined by city (not by order_id). Then timezone transitively depends on order_id through city — a 3NF violation.

SQL
-- Suppose customers had: city, city_timezone (timezone depends on city, not customer_id)

-- 3NF violation in customers:
CREATE TABLE customers_2nf (
  id            INT          NOT NULL AUTO_INCREMENT,
  name          VARCHAR(100) NOT NULL,
  city          VARCHAR(100),
  city_timezone VARCHAR(50), -- depends on city, not on customer_id -> transitive dependency!
  PRIMARY KEY (id)
);

-- 3NF fix: move city info to its own table
CREATE TABLE cities (
  id       INT          NOT NULL AUTO_INCREMENT,
  name     VARCHAR(100) NOT NULL,
  timezone VARCHAR(50)  NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE customers (
  id      INT          NOT NULL AUTO_INCREMENT,
  name    VARCHAR(100) NOT NULL,
  city_id INT,
  PRIMARY KEY (id),
  FOREIGN KEY (city_id) REFERENCES cities (id)
);
Boyce-Codd Normal Form (BCNF)

BCNF is a slightly stricter version of 3NF. A table is in BCNF if, for every functional dependency X -> Y, X is a superkey (X alone uniquely identifies a row). BCNF violations are rare in typical OLTP schemas but can occur with overlapping candidate keys.

SQL
-- Classic BCNF example: teachers, courses, rooms
-- A teacher teaches one course at a time.
-- A room is only assigned to one course at a time.
-- (teacher, course) -> room  and  (room) -> course  are both valid FDs

-- In 3NF but not BCNF:
CREATE TABLE teaching_schedule_bad (
  teacher    VARCHAR(100) NOT NULL,
  course     VARCHAR(100) NOT NULL,
  room       VARCHAR(20)  NOT NULL,
  PRIMARY KEY (teacher, course),   -- teacher + course is unique
  UNIQUE KEY uk_room_course (room) -- room determines course
);

-- BCNF fix: decompose
CREATE TABLE room_assignments (
  room   VARCHAR(20)  NOT NULL,
  course VARCHAR(100) NOT NULL,
  PRIMARY KEY (room)
);

CREATE TABLE teacher_rooms (
  teacher VARCHAR(100) NOT NULL,
  room    VARCHAR(20)  NOT NULL,
  PRIMARY KEY (teacher),
  FOREIGN KEY (room) REFERENCES room_assignments (room)
);
Practical Step-by-Step Normalization

Here is a complete normalization walkthrough for a school database starting from a messy flat table.

SQL
-- Original flat table (unnormalized)
-- student_id | student_name | courses (comma list) | teacher | dept | dept_head
-- 1          | Alice        | Math,Science         | Smith   | STEM | Jones

-- Step 1: 1NF - remove comma-separated courses column
CREATE TABLE enrollments_1nf (
  student_id   INT,
  student_name VARCHAR(100),
  course       VARCHAR(100),
  teacher      VARCHAR(100),
  dept         VARCHAR(50),
  dept_head    VARCHAR(100),
  PRIMARY KEY (student_id, course)
);

-- Step 2: 2NF - student_name depends only on student_id (partial dependency)
--              course/teacher/dept/dept_head depend only on course (partial dependency)
CREATE TABLE students (
  id   INT          NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE courses (
  name     VARCHAR(100) NOT NULL,
  teacher  VARCHAR(100) NOT NULL,
  dept     VARCHAR(50)  NOT NULL,
  dept_head VARCHAR(100) NOT NULL,
  PRIMARY KEY (name)
);

CREATE TABLE enrollments (
  student_id  INT          NOT NULL,
  course_name VARCHAR(100) NOT NULL,
  PRIMARY KEY (student_id, course_name),
  FOREIGN KEY (student_id)  REFERENCES students (id),
  FOREIGN KEY (course_name) REFERENCES courses  (name)
);

-- Step 3: 3NF - dept_head depends on dept, not on course name (transitive dependency)
CREATE TABLE departments (
  name VARCHAR(50)  NOT NULL,
  head VARCHAR(100) NOT NULL,
  PRIMARY KEY (name)
);

CREATE TABLE courses_3nf (
  name    VARCHAR(100) NOT NULL,
  teacher VARCHAR(100) NOT NULL,
  dept    VARCHAR(50)  NOT NULL,
  PRIMARY KEY (name),
  FOREIGN KEY (dept) REFERENCES departments (name)
);
When to Denormalize

Normalization is not always the final word. Sometimes denormalization — intentionally introducing redundancy — improves read performance for specific workloads.

Common reasons to denormalize:

Scenario

Denormalization Technique

Reporting: count of orders per customer

Cache order_count on customers table

Product search: include category name in product row

Duplicate category_name to avoid JOIN

Feed/timeline: avoid deep JOIN chains

Materialized view or summary table

Analytics: aggregate totals by day

Pre-compute daily_stats table via ETL

High-read, rarely-updated data

Embed as JSON or duplicate columns

Warning
Denormalization creates update anomalies. If you duplicate data, you must ensure it is kept in sync — either with triggers, application logic, or a background job. Document every denormalization decision clearly.
Tip
Normalize first, then denormalize deliberately. Never start with a denormalized design "for performance" before you have measured an actual bottleneck.