Home/Learn/MySQL/Normalisation (1NF–BCNF)

Normalisation (1NF–BCNF)

Intermediate

Schema Design

1NF eliminates repeating groups; 2NF removes partial dependencies; 3NF removes transitive dependencies; BCNF handles overlapping candidate keys — normalise for write integrity.

Overview

Normalisation is the process of structuring a relational schema to reduce data redundancy and eliminate update anomalies. Each **Normal Form (NF)** removes a specific class of dependency problem. **1NF**: every column holds atomic values (no arrays, no comma-separated lists) and rows are uniquely identifiable. **2NF**: no non-key column depends on only part of a composite primary key (applies when PK is composite). **3NF**: no non-key column depends on another non-key column (no transitive dependencies). **BCNF**: every determinant is a candidate key (stricter than 3NF, relevant when there are overlapping candidate keys). In practice most OLTP schemas target 3NF. Denormalisation is a deliberate trade-off to improve read performance at the cost of controlled redundancy.

1NF and 2NF: Atomic Values and Full Key Dependency

**1NF violation**: storing multiple values in a column (e.g., `tags = "java,spring,jpa"`). Fix: extract to a separate table. **2NF violation**: in a table with composite PK `(order_id, product_id)`, a column `product_name` depends only on `product_id` — it should live in the Products table.

MySQL — 1NF and 2NF normalisation

-- 1NF VIOLATION: repeating groups in one column
CREATE TABLE articles_bad (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    tags VARCHAR(500)   -- "java,spring,jpa"  ← NOT 1NF
);

-- 1NF FIX: extract to an article_tags table
CREATE TABLE article_tags (
    article_id INT,
    tag        VARCHAR(50),
    PRIMARY KEY (article_id, tag),
    FOREIGN KEY (article_id) REFERENCES articles(id)
);

-- 2NF VIOLATION: composite PK, partial dependency
CREATE TABLE order_items_bad (
    order_id     INT,
    product_id   INT,
    quantity     INT,
    product_name VARCHAR(100),  -- depends only on product_id, NOT the full PK
    PRIMARY KEY (order_id, product_id)
);

-- 2NF FIX: move product_name to the products table
CREATE TABLE order_items (
    order_id   INT,
    product_id INT,
    quantity   INT,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

3NF: Remove Transitive Dependencies

A **transitive dependency** exists when column C depends on column B, which depends on the PK A — making C indirectly dependent on A through B. Classic example: storing `zip_code` and `city` in the same table where `city` is determined by `zip_code`, not by the PK. Fix: extract `zip_code → city` to a separate table.

MySQL — 3NF: remove transitive dependency

-- 3NF VIOLATION: city determined by zip_code, not by order_id
CREATE TABLE orders_bad (
    order_id   INT PRIMARY KEY,
    customer_id INT,
    zip_code   VARCHAR(10),
    city       VARCHAR(100)   -- depends on zip_code, not order_id
);

-- Update anomaly: if a zip code changes city, every order row must update

-- 3NF FIX: extract zip → city mapping
CREATE TABLE zip_codes (
    zip_code VARCHAR(10) PRIMARY KEY,
    city     VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT,
    zip_code    VARCHAR(10),
    FOREIGN KEY (zip_code) REFERENCES zip_codes(zip_code)
);

BCNF and Practical Denormalisation

BCNF is violated when a table has multiple overlapping candidate keys and a non-trivial dependency exists where the determinant is not a candidate key. BCNF is stricter than 3NF and sometimes requires decompositions that reintroduce anomalies — 3NF is usually the practical target. **Denormalisation** is the deliberate addition of redundancy (e.g., materialising a count column, storing derived data) to avoid expensive JOINs on hot read paths.

MySQL — BCNF and controlled denormalisation

-- BCNF violation example: Student can have one advisor per subject;
-- each advisor specialises in one subject only
-- Candidate keys: (student, subject) and (student, advisor)
-- But advisor → subject is a functional dependency where advisor is NOT a candidate key
-- → BCNF violated

-- In practice: denormalise for read performance on hot paths
-- Example: store order_count on customer instead of counting every time
ALTER TABLE customers ADD COLUMN order_count INT NOT NULL DEFAULT 0;

-- Maintain consistency with application logic or triggers:
DELIMITER $$
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders FOR EACH ROW
BEGIN
    UPDATE customers SET order_count = order_count + 1
    WHERE id = NEW.customer_id;
END$$
DELIMITER ;

-- Accept the redundancy trade-off:
-- Normalised:    1 table, always consistent, needs COUNT(*) JOIN for reads
-- Denormalised:  order_count column, fast reads, maintenance overhead

Key Points to Remember

11NF: atomic column values, no repeating groups, unique rows
22NF: every non-key column depends on the entire composite primary key (no partial dependency)
33NF: no non-key column depends on another non-key column (no transitive dependency)
4BCNF: every determinant must be a candidate key (handles overlapping candidate keys)
5Most OLTP schemas target 3NF; BCNF splits further but may reintroduce other anomalies
6Denormalisation is a deliberate trade-off — materialised columns/tables for read-heavy paths

Interview Questions

What is a partial dependency and which normal form eliminates it?

EasyInfosys

Explain a transitive dependency with an example and how 3NF resolves it.

MediumAmazon

What is the difference between 3NF and BCNF?

HardOracle

When would you intentionally denormalise a schema?

MediumShopify

How would you design a schema for tags on blog posts to comply with 1NF?

EasyThoughtWorks

Ask Aria about Normalisation (1NF–BCNF)

Your personal AI tutor — ask anything about this concept

Revision Status

Personal Notes

Discussion

Loading discussion…

Foreign Keys & Referential Integrity

Denormalisation