Skip to main content

Chapter 11: Database Normalization — First Through Third Normal Form, and When to Denormalize

What you'll learn in this chapter​

  • Explain, in your own words, the purpose of normalization (eliminating update, insertion, and deletion anomalies)
  • Decompose a given unnormalized table step by step, up through third normal form
  • Distinguish partial functional dependency from transitive functional dependency
  • Make a judgment call about selective denormalization for performance or operational reasons
Prerequisites

You should understand Chapter 10: Key Concepts. This chapter is the core of this guide, covering the central ideas behind design.

What Is Normalization?​

Normalization is a design technique for eliminating duplication and contradictions in data. By decomposing tables according to a series of rules (normal forms), you bring your data into a state you can manage safely.

Why Normalization Is Needed — Three Anomalies​

A table that isn't normalized (in an unnormalized form) is prone to the following three anomalies.

An Example of a Problematic Design​

A design that crams sales into a single table
CREATE TABLE sales_unnormalized (
    sale_id          INTEGER PRIMARY KEY,
    sale_date        DATE NOT NULL,
    customer_name    VARCHAR(100) NOT NULL,
    customer_address VARCHAR(200),
    customer_phone   VARCHAR(20),
    product_name     VARCHAR(100) NOT NULL,
    product_price    NUMERIC(10, 2) NOT NULL,
    quantity         INTEGER NOT NULL,
    total_amount     NUMERIC(12, 2) NOT NULL
);

INSERT INTO sales_unnormalized VALUES
    (1, '2026-01-01', 'Taro Yamada', 'Tokyo', '090-1111-1111', 'Laptop', 89800, 1, 89800),
    (2, '2026-01-02', 'Taro Yamada', 'Tokyo', '090-1111-1111', 'Mouse',  2980, 2, 5960),
    (3, '2026-01-03', 'Hanako Suzuki', 'Osaka', '080-2222-2222', 'Laptop', 89800, 1, 89800);

Three Anomalies That Occur​

AnomalyExample
Update anomalyIf Yamada moves, you need to update every one of Yamada's rows (2, in the example above). If you forget to update even one, the same "Taro Yamada" ends up with two different addresses
Insertion anomalyYou can't register a new customer as a "customer" before they've ordered a product (sales_unnormalized is a sales table, so you can't add a row without a sale_id)
Deletion anomalyIf you delete Yamada's last sale, you also lose Yamada's address and phone information (if the customer information existed only in this table)

To eliminate these anomalies, you decompose the table according to the normal forms.

Functional Dependency — The Language of Normalization​

The key to understanding normalization is functional dependency.

A → B (the relationship "once A is known, B is uniquely determined too")

"Once you know the employee number, the employee name is uniquely determined" (there's no case where the same employee number maps to a different employee name) is written as employee_number → employee_name.

Partial and Transitive Functional Dependency​

TypeDescriptionExample
Full functional dependencyDepends on the whole primary key(order_id, product_id) → quantity
Partial functional dependencyDetermined by only part of the primary keyIf the primary key is (order_id, product_id), customer_name is determined by order_id alone
Transitive functional dependencyWhen A → B → C, then A → C also holdsWhen employee_id → department_id → department_name, then employee_id → department_name

Partial functional dependency and transitive functional dependency are, respectively, what second normal form and third normal form eliminate.

First Normal Form (1NF) — Cells Hold Only a Single Value​

First normal form requires satisfying the following conditions.

  1. Each cell (a column's value) holds only an atomic value (it can't be broken down any further)
  2. There are no repeating groups (no row has a repetition like "multiple products")
  3. Each row is unique (a primary key exists)

An Example of First Normal Form​

Converting to first normal form
CREATE TABLE orders_1nf (
    order_id         INTEGER NOT NULL,
    customer_name    VARCHAR(100) NOT NULL,
    product_name     VARCHAR(100) NOT NULL,
    quantity         INTEGER NOT NULL,
    unit_price       NUMERIC(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_name)
);
Do array and JSON columns violate first normal form?

Using PostgreSQL's INTEGER[] array type or JSONB type can make it look like a single cell holds multiple values. Strictly speaking, this violates first normal form, but it's often used as a practical, modern compromise for handling "semi-structured data." As long as you understand the theory of normalization and use it deliberately, there's no problem. On the other hand, beginners should avoid stuffing everything into JSON just "to reduce the number of columns" — it makes searching and joining difficult.

Second Normal Form (2NF) — Eliminating Partial Functional Dependency​

Second normal form is the state of "first normal form, plus no partial functional dependency."

If the primary key is a single column, satisfying first normal form automatically satisfies second normal form too (a partial functional dependency can only occur with a composite primary key). The problematic case is a composite primary key.

An Example of Partial Functional Dependency​

The orders_1nf table's primary key is the composite (order_id, product_name). Here, customer_name is determined by order_id alone (since a single order has only one customer). This is a partial functional dependency.

Steps for Second Normal Form​

Converting to second normal form
-- Orders table (attributes determined by order_id alone)
CREATE TABLE orders_2nf (
    order_id         INTEGER PRIMARY KEY,
    customer_name    VARCHAR(100) NOT NULL,
    customer_address VARCHAR(200),
    order_date       DATE NOT NULL
);

-- Order line items table (fully functionally dependent on the composite key (order_id, product_name))
CREATE TABLE order_items_2nf (
    order_id      INTEGER NOT NULL REFERENCES orders_2nf(order_id),
    product_name  VARCHAR(100) NOT NULL,
    quantity      INTEGER NOT NULL,
    unit_price    NUMERIC(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_name)
);

Since customer_name moved to orders_2nf (whose primary key is order_id alone), the partial functional dependency is resolved.

Third Normal Form (3NF) — Eliminating Transitive Functional Dependency​

Third normal form is the state of "second normal form, plus no transitive functional dependency."

An Example of Transitive Functional Dependency​

Suppose the employees table has employee_id as its primary key, and also holds department_id and department_name.

From the relationship employee_id → department_id → department_name, employee_id → department_name holds transitively. In this case, you remove department_name from the employees table and separate it into a departments table.

Steps for Third Normal Form​

Converting to third normal form
-- Employees table (doesn't hold the department name)
CREATE TABLE employees_3nf (
    id              SERIAL PRIMARY KEY,
    name            VARCHAR(100) NOT NULL,
    department_id   INTEGER NOT NULL REFERENCES departments_3nf(id),
    hire_date       DATE NOT NULL
);

-- Departments table (the department name is stored here)
CREATE TABLE departments_3nf (
    id        SERIAL PRIMARY KEY,
    name      VARCHAR(50) NOT NULL,
    location  VARCHAR(100)
);

Changing departments_3nf.name doesn't affect employees_3nf at all (getting the department name requires a JOIN). This resolves the update anomaly of "rewriting the department name for 100 people."

A Step-by-Step Normalization Example — Orders on an E-Commerce Site​

Let's walk through going from unnormalized to third normal form with a single example.

Step 0: Unnormalized (Cramming Everything Into One Column)​

order_data column: "Order 1: Taro Yamada (Tokyo), Products: PC×1, Mouse×2"

This can't be searched or aggregated, so it needs to be structured first.

Step 1: First Normal Form​

CREATE TABLE orders_1nf (
    order_id         INTEGER,
    customer_name    VARCHAR(100),
    customer_address VARCHAR(200),
    product_name     VARCHAR(100),
    quantity         INTEGER,
    unit_price       NUMERIC(10, 2),
    PRIMARY KEY (order_id, product_name)
);

Each cell now holds one value, and the repeating group (the array of products) has been expanded into rows.

Step 2: Second Normal Form (Resolving Partial Functional Dependency)​

-- Orders table (gathers the attributes determined by order_id)
CREATE TABLE orders_2nf (
    order_id         INTEGER PRIMARY KEY,
    customer_name    VARCHAR(100) NOT NULL,
    customer_address VARCHAR(200),
    order_date       DATE NOT NULL
);

-- Order line items table
CREATE TABLE order_items_2nf (
    order_id      INTEGER NOT NULL REFERENCES orders_2nf(order_id),
    product_name  VARCHAR(100) NOT NULL,
    quantity      INTEGER NOT NULL,
    unit_price    NUMERIC(10, 2) NOT NULL,
    PRIMARY KEY (order_id, product_name)
);

Step 3: Third Normal Form (Resolving Transitive Functional Dependency)​

-- Customers table (separated from orders)
CREATE TABLE customers_3nf (
    id        SERIAL PRIMARY KEY,
    name      VARCHAR(100) NOT NULL,
    address   VARCHAR(200)
);

-- Products table (separated from the order line items)
CREATE TABLE products_3nf (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    unit_price  NUMERIC(10, 2) NOT NULL
);

-- Orders table (a foreign key to customers)
CREATE TABLE orders_3nf (
    id           SERIAL PRIMARY KEY,
    customer_id  INTEGER NOT NULL REFERENCES customers_3nf(id),
    order_date   DATE NOT NULL
);

-- Order line items table (foreign keys to orders and products; price is a snapshot)
CREATE TABLE order_items_3nf (
    order_id     INTEGER NOT NULL REFERENCES orders_3nf(id),
    product_id   INTEGER NOT NULL REFERENCES products_3nf(id),
    quantity     INTEGER NOT NULL,
    unit_price   NUMERIC(10, 2) NOT NULL,             -- the price at order time (a snapshot)
    PRIMARY KEY (order_id, product_id)
);

Decomposed this far, a customer moving is just a single-row update to customers_3nf, and a product price change is a single-row update to products_3nf (reflected in future orders; past order_items_3nf rows keep the price at order time) — an operational model that holds together.

BCNF (Boyce-Codd Normal Form) — Stricter Than 3NF​

BCNF satisfies 3NF and adds a stricter condition on top: "the left-hand side of every non-trivial functional dependency is a superkey" (see Wikipedia: Boyce–Codd normal form).

3NF is often enough for business applications

Most business applications are fine with 3NF. BCNF becomes necessary in the special case of "multiple candidate keys whose functional dependencies are complex." That said, decomposing into BCNF sometimes can't preserve dependency preservation, which is itself a trade-off. Since junior engineers encounter this rarely, this guide doesn't cover the detailed decomposition steps — it's enough to remember "aim for 3NF, and consider BCNF for complex cases."

An Example Where BCNF Is Needed (For Reference)​

Consider a case where an employee has multiple work locations (at most one per city), and the work location is recorded by zip code. A single zip code belongs to exactly one city (zip_code → city).

EmployeeCityZip Code
TanakaDowntown10001
TanakaUptown60601
SuzukiDowntown10001

There are two functional dependencies: (employee, city) → zip_code and zip_code → city. There are two candidate keys, (employee, city) and (employee, zip_code), and every attribute belongs to one candidate key or another (all are prime attributes). Since 3NF makes an exception for "dependency on an attribute that belongs to a candidate key" (a prime attribute), this table satisfies 3NF. However, the left-hand side of zip_code → city (zip_code) isn't a superkey, so it doesn't satisfy BCNF — in practice, this means "the zip-code-to-city mapping" gets duplicated every time another row with the same zip code is added, requiring multiple rows to be updated if that mapping is ever corrected. Decomposing into BCNF would split this into an (employee, zip_code) table and a (zip_code, city) table.

The Pros and Cons of Normalization​

Selective Denormalization for Performance​

Normalization is the ideal, but you'll sometimes deliberately denormalize for performance requirements or operational reasons. The modern best practice (oneuptime, denormalization patterns, 2026-01) is "normalize first, then selectively denormalize based on measurement."

An Example of Denormalization​

An aggregation table (denormalized, for reporting)
CREATE TABLE sales_summary (
    id             BIGSERIAL PRIMARY KEY,
    order_id       INTEGER NOT NULL,
    order_date     DATE NOT NULL,
    customer_name  VARCHAR(100) NOT NULL,     -- redundant: duplicates customers.name
    product_name   VARCHAR(100) NOT NULL,     -- redundant: duplicates products.name
    quantity       INTEGER NOT NULL,
    unit_price     NUMERIC(10, 2) NOT NULL,
    total_amount   NUMERIC(12, 2) NOT NULL,   -- redundant: could be derived by computation
    created_at     TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_summary_date    ON sales_summary(order_date);
CREATE INDEX idx_summary_customer ON sales_summary(customer_name);

-- A fast aggregation query (works without a JOIN)
SELECT customer_name, SUM(total_amount) AS total_sales
FROM   sales_summary
WHERE  order_date BETWEEN '2026-01-01' AND '2026-12-31'
GROUP BY customer_name;

This table is dedicated to the purpose of "retrieving past aggregates quickly." It's common to sync it from the original normalized tables via a daily batch job.

Decision Criteria for Denormalization​

AxisKeep it normalizedConsider denormalizing
Update frequencyHigh (updated every second)Low (updated daily)
Read frequencyLowVery high (aggregate reports)
Data-integrity requirementStrongSome drift is acceptable for reporting
Number of JOINs in a queryFew (2-3)Many (10+)
The golden rules of denormalization
  • Don't denormalize from the start: start from normalization, and address it only once measurement reveals a performance bottleneck
  • Be aware you're managing data in two places: you need a mechanism to keep the normalized and denormalized tables in sync
  • Classic approaches: batch sync / materialized views / event-driven updates
  • Be careful with triggers: risk of lock contention and deadlocks (covered in Chapter 14: Many-to-Many)

Exercises​

Exercise 1: Normal-form quiz

Up through which normal form does the following table satisfy? Answer along with your reasoning.

attendances
+------------+-----------+----------------+----------------+--------------+
| employee_id| work_date | department_id  | department_name| work_hours   |
+------------+-----------+----------------+----------------+--------------+
| 1001       | 1/15      | 10             | Sales          | 8.0          |
| 1001       | 1/16      | 10             | Sales          | 7.5          |
| 1002       | 1/15      | 20             | Engineering    | 8.5          |
+------------+-----------+----------------+----------------+--------------+
Primary key: (employee_id, work_date)
Sample answer

This table satisfies first normal form, but not second or third normal form.

First normal form ✓: each cell holds an atomic value, there are no repeating groups, and a primary key exists.

Second normal form ✕: the primary key is the composite (employee_id, work_date). department_id is determined by employee_id alone (it doesn't depend on the work date), so a partial functional dependency exists. Likewise, department_name is also determined by employee_id alone.

Third normal form ✕: even if it satisfied second normal form, there's a transitive functional dependency employee_id → department_id → department_name.

After normalizing:

-- Decomposed up through third normal form
CREATE TABLE departments (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    id             SERIAL PRIMARY KEY,
    department_id  INTEGER NOT NULL REFERENCES departments(id)
);

CREATE TABLE attendances (
    employee_id  INTEGER NOT NULL REFERENCES employees(id),
    date         DATE NOT NULL,
    work_hours   NUMERIC(4, 2) NOT NULL,
    PRIMARY KEY (employee_id, date)
);

A common mistake: simply judging "holding department_id and department_name together violates second normal form." More precisely, the problem is a partial functional dependency — department_id and department_name are determined by employee_id, only part of the primary key.

Alternative approach (handling transfers): if employees can move between departments, one design holds a snapshot of "which department they were in on that day" on the attendances table. Theoretically this is redundant under normalization, but it's a deliberate snapshot to accurately know "which department they were in on that day" later — the same idea as snapshotting a unit price on an order line item.

Exercise 2: Step-by-step normalization

Decompose the following table step by step, from first through second to third normal form. Note explicitly what you eliminate at each stage.

courses
+------------+-------------+-----------+----------------+-------------+-------------+---------+
| student_id | student_name| course_id | course_name    | teacher_id  | teacher_name| fee     |
+------------+-------------+-----------+----------------+-------------+-------------+---------+
| 1          | Yamada      | C101      | Database       | T01         | Tanaka      | 10000   |
| 1          | Yamada      | C102      | Programming    | T02         | Suzuki      | 15000   |
| 2          | Sato        | C101      | Database       | T01         | Tanaka      | 10000   |
+------------+-------------+-----------+----------------+-------------+-------------+---------+
Primary key: (student_id, course_id)
Assumption: each course has exactly one teacher, and a course's fee is fixed
Sample answer

Step 0 (original): satisfies first normal form, but not second or third.

Step 1: Decompose into second normal form (eliminating partial functional dependency)

Out of the primary key (student_id, course_id):

  • student_name is determined by student_id alone → partial functional dependency
  • course_name / teacher_id / teacher_name / fee are determined by course_id alone → partial functional dependency

Decomposition:

-- Students table (attributes determined by student_id)
CREATE TABLE students_2nf (
    id    SERIAL PRIMARY KEY,
    name  VARCHAR(100) NOT NULL
);

-- Courses table (attributes determined by course_id; still holds teacher_name and fee)
CREATE TABLE courses_2nf (
    id          VARCHAR(10) PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    teacher_id  VARCHAR(10) NOT NULL,
    teacher_name VARCHAR(100) NOT NULL,
    fee         NUMERIC(10, 2) NOT NULL
);

-- Enrollments table (only attributes fully functionally dependent on the composite key)
CREATE TABLE enrollments_2nf (
    student_id  INTEGER NOT NULL REFERENCES students_2nf(id),
    course_id   VARCHAR(10) NOT NULL REFERENCES courses_2nf(id),
    PRIMARY KEY (student_id, course_id)
);

Step 2: Decompose into third normal form (eliminating transitive functional dependency)

In courses_2nf, there's a transitive functional dependency course_id → teacher_id → teacher_name (one course has one teacher, and one teacher has one teacher name).

-- Teachers table (separating the attributes determined by teacher_id)
CREATE TABLE teachers_3nf (
    id    VARCHAR(10) PRIMARY KEY,
    name  VARCHAR(100) NOT NULL
);

-- Courses table (no longer holds the teacher's name)
CREATE TABLE courses_3nf (
    id          VARCHAR(10) PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    teacher_id  VARCHAR(10) NOT NULL REFERENCES teachers_3nf(id),
    fee         NUMERIC(10, 2) NOT NULL
);

-- The students and enrollments tables are unchanged

Final result:

  • students_3nf (id, name)
  • courses_3nf (id, name, teacher_id, fee)
  • teachers_3nf (id, name)
  • enrollments_3nf (student_id, course_id) — note: the primary key is composite

Common mistakes:

  1. Putting fee on enrollments: given the assumption "a course's fee is fixed," it belongs on courses. Conversely, if "the fee varies per student" (e.g., a discount), putting it on enrollments could be the right call
  2. Leaving student_name on enrollments too: this violates second normal form. Student information should be consolidated on students

Alternative approach (for real-world use): if a course's fee changes over time, hold "the fee at the time of enrollment" as a snapshot on enrollments. This is a design decision that trades off normalization for chronological accuracy — the same pattern as an order line item's unit price.

Summary​

Here's a recap of what this chapter covered.

  • Normalization is a design technique for eliminating data duplication and contradictions (update, insertion, and deletion anomalies)
  • First normal form: cells hold atomic values, no repeating groups, has a primary key
  • Second normal form: 1NF + eliminates partial functional dependency (separates out attributes determined by only part of a composite primary key)
  • Third normal form: 2NF + eliminates transitive functional dependency (separates out chained dependencies)
  • 3NF is often enough for business applications (BCNF is for complex special cases)
  • You'll sometimes deliberately denormalize for performance requirements, but start from normalization first, and decide based on measurement

The next chapter covers how to create ER diagrams, which visually express a design.

Next chapter

Chapter 12: How to Create ER Diagrams