Introduction

A relational database can store the correct information and still have a poor structure. When unrelated facts are combined in one table, the same values are often repeated across many rows.

Repeated data wastes storage, complicates updates, and creates opportunities for contradictory information.

Normalization organizes data into related tables so that each important fact has an appropriate and controlled place.

Denormalization deliberately introduces selected duplication or precomputed data to support specific read-performance requirements.

In your System Design curriculum, Normalization and Denormalization is Topic 5.2 under Relational Data Modeling & SQL. It follows entities and relationships and precedes keys and constraints, ACID, transactions, isolation, indexes, query plans, and connection pools.

Prerequisites

What Is Normalization?

Normalization is a systematic process for organizing relational data to reduce unnecessary duplication and prevent insertion, update, and deletion anomalies.

The process usually decomposes a large relation into smaller related relations based on keys and functional dependencies.

The principal goals are:

• Store each fact in an appropriate place
• Reduce unnecessary repetition
• Prevent contradictory values
• Support reliable inserts, updates, and deletes
• Clarify entity and relationship ownership
• Improve maintainability
• Preserve data integrity

Data Redundancy

Data redundancy occurs when the same fact is stored repeatedly in several rows or columns.

Redundant Order Data

The customer email is repeated for every purchased product. Product names can also repeat across many orders.

If one repeated value is updated and another is missed, the database can contain contradictory versions of the same fact.

Modification Anomalies

Poorly structured relations commonly produce three types of anomalies.

Insertion Anomaly

Problem:

Product data exists only inside order rows.

A new product cannot be stored
until somebody places an order.

Update Anomaly

Problem:

A customer's email occurs in 200 order rows.

The email changes.

All 200 rows must be updated.

If one row is missed,
two email values exist for one customer.

Deletion Anomaly

Problem:

A product appears only in one order row.

Deleting that order removes
the only stored product information.

Functional Dependencies

A functional dependency exists when the value of one attribute or attribute set determines the value of another attribute.

If attribute set \(X\) determines attribute set \(Y\), the dependency is written as:

\[ X \rightarrow Y \]

Customer Dependency

customer_id -> display_name
customer_id -> email_address
customer_id -> customer_status

For one customer ID, the model expects one corresponding display name, email address, and status at a given point in the modeled state.

Order-line Dependency

(order_id, line_number)
    ->
product_id
quantity
unit_price

The complete composite key determines the attributes of one order line.

Determinants

In the dependency \(X \rightarrow Y\), \(X\) is called the determinant.

Dependency:

product_id -> product_name


Determinant:

product_id


Dependent attribute:

product_name

Understanding determinants helps identify which facts belong together in one relation.

Normal Forms

Normal forms are progressive relational-design conditions. Each level addresses particular dependency and redundancy problems.

First Normal Form

A relation in First Normal Form, abbreviated as 1NF, stores one value belonging to the declared domain in each row-and-column intersection and avoids repeating groups.

First Normal Form Violation

The telephone column contains a list rather than one value from its intended telephone-number domain.

First Normal Form Design

CREATE TABLE customers
(
    customer_id BIGINT PRIMARY KEY,
    display_name VARCHAR(200) NOT NULL
);

CREATE TABLE customer_phone_numbers
(
    phone_number_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    phone_number VARCHAR(30) NOT NULL,
    phone_type VARCHAR(20) NOT NULL,

    CONSTRAINT fk_customer_phone_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id),

    CONSTRAINT uq_customer_phone
        UNIQUE
        (
            customer_id,
            phone_number
        )
);

Each telephone number is represented by a separate row associated with the customer.

Repeating Groups

product_1 VARCHAR(100),
quantity_1 INT,
product_2 VARCHAR(100),
quantity_2 INT,
product_3 VARCHAR(100),
quantity_3 INT

This design imposes an arbitrary maximum number of products and requires structural changes when another item is added.

CREATE TABLE order_lines
(
    order_id BIGINT NOT NULL,
    line_number INT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,

    PRIMARY KEY
    (
        order_id,
        line_number
    )
);

Second Normal Form

A relation is in Second Normal Form, abbreviated as 2NF, when it is in 1NF and every non-key attribute depends on the complete candidate key rather than only part of a composite key.

Partial dependency is relevant when a candidate key contains more than one attribute.

Relation with Partial Dependencies

ORDER_PRODUCT

Composite key:
(order_id, product_id)

Attributes:
order_date
customer_id
product_name
current_price
quantity

Dependencies include:

order_id
    ->
order_date
customer_id


product_id
    ->
product_name
current_price


(order_id, product_id)
    ->
quantity

The order attributes depend only on order_id. Product attributes depend only on product_id. Only quantity depends on the entire composite key.

Decomposition into Second Normal Form

CREATE TABLE orders
(
    order_id BIGINT PRIMARY KEY,
    customer_id BIGINT NOT NULL,
    order_date DATE NOT NULL
);

CREATE TABLE products
(
    product_id BIGINT PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL,
    current_price DECIMAL(18, 2) NOT NULL
);

CREATE TABLE order_lines
(
    order_id BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,

    PRIMARY KEY
    (
        order_id,
        product_id
    ),

    FOREIGN KEY (order_id)
        REFERENCES orders (order_id),

    FOREIGN KEY (product_id)
        REFERENCES products (product_id)
);

Third Normal Form

A relation is in Third Normal Form, abbreviated as 3NF, when it satisfies 2NF and does not contain an inappropriate transitive dependency in which a non-key fact depends on another non-key fact.

Transitive Dependency Example

EMPLOYEE

employee_id
employee_name
department_id
department_name
department_location

Dependencies include:

employee_id
    ->
employee_name
department_id


department_id
    ->
department_name
department_location

Department name and location describe the department, not the employee. They depend on the employee key indirectly through department_id.

Third Normal Form Decomposition

CREATE TABLE departments
(
    department_id BIGINT PRIMARY KEY,
    department_name VARCHAR(200) NOT NULL,
    department_location VARCHAR(200) NOT NULL
);

CREATE TABLE employees
(
    employee_id BIGINT PRIMARY KEY,
    employee_name VARCHAR(200) NOT NULL,
    department_id BIGINT NOT NULL,

    CONSTRAINT fk_employees_department
        FOREIGN KEY (department_id)
        REFERENCES departments (department_id)
);

Department facts are stored once in the department relation. Employees reference the appropriate department.

The Key, the Whole Key, and Nothing but the Key

A memorable informal summary of the early normal forms is:

Every non-key fact should depend on:

The key
The whole key
And nothing but the key

This phrase is a learning aid rather than a complete formal definition. Formal normalization requires identifying all candidate keys and functional dependencies.

Boyce-Codd Normal Form

Boyce-Codd Normal Form, abbreviated as BCNF, is a stricter condition under which every determinant in a nontrivial functional dependency is a candidate key.

Example Scenario

TEACHING_ASSIGNMENT

student_id
course_id
instructor_id


Business rules:

A student can take a course.

Each instructor teaches one course.

A course can have several instructors.

Possible dependencies include:

(student_id, course_id)
    ->
instructor_id


instructor_id
    ->
course_id

The instructor determines the course, but the instructor alone is not necessarily a candidate key for the complete assignment relation. This kind of dependency can require further decomposition.

Possible Decomposition

CREATE TABLE instructor_courses
(
    instructor_id BIGINT PRIMARY KEY,
    course_id BIGINT NOT NULL,

    FOREIGN KEY (course_id)
        REFERENCES courses (course_id)
);

CREATE TABLE student_instructors
(
    student_id BIGINT NOT NULL,
    instructor_id BIGINT NOT NULL,

    PRIMARY KEY
    (
        student_id,
        instructor_id
    ),

    FOREIGN KEY (student_id)
        REFERENCES students (student_id),

    FOREIGN KEY (instructor_id)
        REFERENCES instructor_courses (instructor_id)
);

The exact decomposition depends on the complete business rules and candidate keys. Do not normalize from a simplified example without confirming the real dependencies.

Fourth Normal Form

Fourth Normal Form, abbreviated as 4NF, addresses independent multivalued dependencies stored in one relation.

Independent Multi-valued Facts

EMPLOYEE_SKILL_LANGUAGE

employee_id
skill
language


Employee 1 skills:

SQL
Python


Employee 1 languages:

English
Bengali

If skills and languages are independent, storing both in one relation produces all combinations:

Employee 1, SQL, English
Employee 1, SQL, Bengali
Employee 1, Python, English
Employee 1, Python, Bengali

The combinations imply no meaningful relationship between one skill and one language.

Fourth Normal Form Decomposition

CREATE TABLE employee_skills
(
    employee_id BIGINT NOT NULL,
    skill_id BIGINT NOT NULL,

    PRIMARY KEY
    (
        employee_id,
        skill_id
    )
);

CREATE TABLE employee_languages
(
    employee_id BIGINT NOT NULL,
    language_code VARCHAR(20) NOT NULL,

    PRIMARY KEY
    (
        employee_id,
        language_code
    )
);

Fifth Normal Form

Fifth Normal Form, abbreviated as 5NF, addresses complex join dependencies where a relation can be decomposed further into smaller relations and reconstructed through joins without introducing incorrect combinations.

These scenarios are less common in ordinary transactional application design. They require careful analysis of the complete business rules.

Conceptual example involving:

Supplier
Product
Region


Possible facts:

Supplier can supply Product.
Supplier operates in Region.
Product is approved in Region.


Question:

Does the combination of these binary facts
always imply a valid supplier-product-region fact?

The answer depends on the domain. A decomposition is correct only when its joins preserve exactly the valid original facts.

Lossless Decomposition

A decomposition is lossless when joining the decomposed relations reconstructs the original valid information without losing rows or creating false combinations.

Original relation
      |
      v
Decompose into smaller relations
      |
      v
Join smaller relations
      |
      v
Recover exactly the original facts

Dependency Preservation

A decomposition preserves dependencies when important business dependencies can be enforced directly on the decomposed relations without requiring expensive joins for every validation.

Desired outcome:

- Decomposition is lossless.
- Important dependencies remain enforceable.
- Keys and constraints remain understandable.
- Queries can reconstruct required business views.

A theoretically more normalized decomposition can be less practical when critical constraints become difficult to enforce. The final design requires careful trade-off analysis.

Running Example: Unnormalized Order Data

ORDER_DATA

order_id
order_date
customer_id
customer_name
customer_email
customer_city
product_ids
product_names
quantities
unit_prices
salesperson_id
salesperson_name

This structure contains several problems:

• Product attributes contain lists
• Customer data repeats across orders
• Product data repeats across order lines
• Salesperson data repeats across orders
• Order-line facts are mixed with order-level facts
• Customer facts are mixed with transaction facts

Convert the Example to First Normal Form

Create one row for every order line and use atomic values.

ORDER_DATA_1NF

order_id
product_id
order_date
customer_id
customer_name
customer_email
customer_city
product_name
quantity
unit_price
salesperson_id
salesperson_name

A possible composite key is (order_id, product_id) when one product appears at most once per order. If repeated products are allowed, an order-line number is required.

The table now has atomic values, but customer, product, salesperson, and order facts still repeat.

Convert the Example to Second Normal Form

Remove facts that depend on only part of the composite order-line key.

ORDERS

order_id
order_date
customer_id
customer_name
customer_email
customer_city
salesperson_id
salesperson_name


PRODUCTS

product_id
product_name


ORDER_LINES

order_id
product_id
quantity
unit_price

Order-level attributes move to ORDERS. Product-level attributes move to PRODUCTS. Order-line facts remain in ORDER_LINES.

Convert the Example to Third Normal Form

Customer and salesperson details do not directly describe the order key. They describe customer and salesperson entities.

CUSTOMERS

customer_id
customer_name
customer_email
customer_city


SALESPEOPLE

salesperson_id
salesperson_name


ORDERS

order_id
order_date
customer_id
salesperson_id


PRODUCTS

product_id
product_name


ORDER_LINES

order_id
line_number
product_id
quantity
unit_price

Normalized SQL Schema

CREATE TABLE customers
(
    customer_id BIGINT PRIMARY KEY,
    customer_name VARCHAR(200) NOT NULL,
    customer_email VARCHAR(254) NOT NULL,
    customer_city VARCHAR(100) NOT NULL,

    CONSTRAINT uq_customers_email
        UNIQUE (customer_email)
);

CREATE TABLE salespeople
(
    salesperson_id BIGINT PRIMARY KEY,
    salesperson_name VARCHAR(200) NOT NULL
);

CREATE TABLE products
(
    product_id BIGINT PRIMARY KEY,
    product_name VARCHAR(200) NOT NULL
);

CREATE TABLE orders
(
    order_id BIGINT PRIMARY KEY,
    order_date DATE NOT NULL,
    customer_id BIGINT NOT NULL,
    salesperson_id BIGINT NOT NULL,

    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id),

    CONSTRAINT fk_orders_salesperson
        FOREIGN KEY (salesperson_id)
        REFERENCES salespeople (salesperson_id)
);

CREATE TABLE order_lines
(
    order_id BIGINT NOT NULL,
    line_number INT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity INT NOT NULL,
    unit_price DECIMAL(18, 2) NOT NULL,

    PRIMARY KEY
    (
        order_id,
        line_number
    ),

    CONSTRAINT fk_order_lines_order
        FOREIGN KEY (order_id)
        REFERENCES orders (order_id),

    CONSTRAINT fk_order_lines_product
        FOREIGN KEY (product_id)
        REFERENCES products (product_id),

    CONSTRAINT ck_order_lines_quantity
        CHECK (quantity > 0),

    CONSTRAINT ck_order_lines_price
        CHECK (unit_price >= 0)
);

Historical Snapshots Are Not Always Denormalization

Repeating data is not automatically a normalization mistake. Sometimes two similar-looking values represent different facts.

products.current_price:

The product's current selling price.


order_lines.unit_price:

The price agreed when the order was placed.

The values can differ legitimately because they describe different points in time and different business facts.

Transaction Snapshot

CREATE TABLE order_lines
(
    order_id BIGINT NOT NULL,
    line_number INT NOT NULL,
    product_id BIGINT NOT NULL,
    product_description_snapshot VARCHAR(200) NOT NULL,
    quantity INT NOT NULL,
    unit_price_at_order DECIMAL(18, 2) NOT NULL,

    PRIMARY KEY
    (
        order_id,
        line_number
    )
);

What Is Denormalization?

Denormalization deliberately stores selected duplicated, aggregated, or prejoined data to support a defined access pattern.

Denormalization can reduce joins or repeated calculations for high-volume reads, but it introduces additional write and consistency responsibilities.

Normalization vs Denormalization

When to Consider Denormalization

Denormalization can be considered when:

• A measured critical read query remains expensive after appropriate indexing
• A frequently requested view requires many repeated joins
• An aggregate is calculated continuously from a large dataset
• A reporting workload needs a read-optimized model
• A distributed read path cannot synchronously join data across services
• A historical snapshot must remain stable
• A precomputed projection improves a defined user workflow

Denormalization should not be the first response to a slow query. First inspect the query plan, indexes, selected columns, filters, pagination, and database configuration.

When Not to Denormalize

Avoid denormalization when:

• No performance bottleneck has been measured
• The duplicated values change frequently
• No synchronization owner exists
• Strong immediate consistency is mandatory
• The normalized query can be fixed with an appropriate index
• The team cannot monitor and repair data drift
• The additional write complexity exceeds the read benefit

Precomputed Aggregate

An order total can be calculated from its lines whenever the order is retrieved.

SELECT
    order_id,
    SUM(quantity * unit_price)
        AS calculated_total
FROM order_lines
WHERE
    order_id = :order_id
GROUP BY
    order_id;

A denormalized design can store the total on the order row:

ALTER TABLE orders
ADD COLUMN total_amount DECIMAL(18, 2) NOT NULL;

The stored total must be updated whenever a price, quantity, discount, tax, fee, or line membership changes.

Transactional Maintenance

Begin transaction

1. Insert or update order line.
2. Recalculate order total.
3. Update orders.total_amount.
4. Validate order invariants.

Commit transaction

Duplicated Display Value

A frequently displayed customer name can be copied into an order read model to avoid a repeated join.

CREATE TABLE order_read_model
(
    order_id BIGINT PRIMARY KEY,
    order_number VARCHAR(30) NOT NULL,
    customer_id BIGINT NOT NULL,
    customer_display_name VARCHAR(200) NOT NULL,
    order_status VARCHAR(30) NOT NULL,
    total_amount DECIMAL(18, 2) NOT NULL,
    ordered_at TIMESTAMP NOT NULL
);

The design must answer:

• Is the copied name a historical snapshot or a current projection?
• Which table is authoritative?
• When is the copy updated?
• What temporary staleness is acceptable?
• How is drift detected and repaired?

Materialized Read Models

A read model stores data shaped for a specific query or screen.

Normalized write model:

Customers
Orders
Order Lines
Payments
Shipments


Read model:

Order Summary

- Order ID
- Customer display name
- Current order status
- Payment status
- Shipment status
- Total amount

The normalized model remains the authoritative transactional store. The read model is rebuilt or updated from authoritative changes.

Materialized Views

Some database systems support materialized views that store query results physically and refresh them according to a defined policy.

CREATE MATERIALIZED VIEW customer_order_summary AS
SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS order_count,
    SUM(o.total_amount) AS lifetime_order_value
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
GROUP BY
    c.customer_id,
    c.customer_name;

Syntax and refresh capabilities differ between database platforms. The refresh frequency determines how current the stored results are.

Synchronization Strategies

Eventual Consistency

An asynchronously updated read model can temporarily differ from the authoritative normalized data.

Authoritative customer name changes
        |
        v
Transaction commits
        |
        v
Change event is published
        |
        v
Projection consumer processes event
        |
        v
Order read model is updated

Between the source commit and projection update, readers can observe the old value.

The contract should define:

• Which source is authoritative
• Expected staleness
• Update ordering
• Retry behaviour
• Duplicate-event handling
• Rebuild and reconciliation procedures

Detecting Data Drift

Data drift occurs when a denormalized representation no longer agrees with its authoritative source.

SELECT
    orm.order_id,
    orm.customer_display_name
        AS copied_name,
    c.customer_name
        AS authoritative_name
FROM order_read_model AS orm
INNER JOIN orders AS o
    ON o.order_id = orm.order_id
INNER JOIN customers AS c
    ON c.customer_id = o.customer_id
WHERE
    orm.customer_display_name
        <> c.customer_name;

A reconciliation job can measure differences and repair them according to the read model's synchronization policy.

Read and Write Amplification

Normalized designs can increase read joins. Denormalized designs can increase write amplification. The correct design depends on workload, consistency, maintainability, and failure requirements.

Transactional vs Analytical Models

One application can use a normalized transactional model and a separate denormalized analytical model rather than forcing one schema to optimize every workload.

Star-schema Example

Analytical systems commonly organize measurable events in a fact table and descriptive information in dimension tables.

FACT_SALES
   |
   +-- DATE_DIMENSION
   |
   +-- CUSTOMER_DIMENSION
   |
   +-- PRODUCT_DIMENSION
   |
   +-- STORE_DIMENSION

Dimension tables can intentionally contain descriptive attributes optimized for analytical queries. This is a different workload and design objective from normalized transactional processing.

Join Cost Is Not Automatically a Problem

A normalized query containing joins is not necessarily slow. Relational databases are designed to join tables using indexes and optimized execution plans.

SELECT
    o.order_number,
    c.customer_name,
    o.order_status,
    o.total_amount
FROM orders AS o
INNER JOIN customers AS c
    ON c.customer_id = o.customer_id
WHERE
    o.order_id = :order_id;

Before denormalizing, verify:

• The foreign-key columns are indexed appropriately
• The query predicates are selective
• The query retrieves only required columns
• The execution plan uses appropriate access paths
• Statistics are current
• The request is paginated where applicable
• The actual latency is a user-facing bottleneck

Integrity after Denormalization

Every denormalized value needs a documented integrity contract.

Security and Denormalized Data

Duplicating data expands the number of locations that require access control, encryption, retention, auditing, and deletion handling.

Review whether denormalization duplicates:

• Personal information
• Payment information
• Authentication or authorization data
• Confidential business data
• Tenant identifiers
• Deletion-controlled information

Observability

Useful normalization and denormalization metrics include:

• Duplicate-value conflict count
• Foreign-key violation count
• Read-model update delay
• Projection processing failures
• Source-to-copy drift count
• Materialized-view refresh duration
• Normalized query latency
• Denormalized query latency
• Write amplification
• Reconciliation repair count
• Read-model rebuild duration
• Storage consumed by duplicated data

Normalization Workflow

1. Identify the relation's intended meaning.
2. Identify candidate keys.
3. List functional and multivalued dependencies.
4. Identify repeating groups and non-atomic attributes.
5. Move independent repeated values into related rows.
6. Identify partial dependencies on composite-key components.
7. Move partial dependencies into their own relations.
8. Identify transitive dependencies through non-key attributes.
9. Move separate entity facts into their own relations.
10. Verify lossless decomposition.
11. Verify dependency preservation.
12. Add primary, foreign, unique, and check constraints.
13. Test realistic inserts, updates, deletes, and joins.

Denormalization Workflow

1. Begin with a correct normalized model.
2. Measure the production or representative workload.
3. Identify one verified read bottleneck.
4. Inspect indexes, query plans, filters, and pagination first.
5. Define the exact duplicated or precomputed value.
6. Identify the authoritative source.
7. Choose a synchronization mechanism.
8. Define acceptable staleness.
9. Add reconciliation and rebuild procedures.
10. Protect duplicated sensitive data.
11. Load-test read and write paths.
12. Document the reason and ownership.

Common Normalization and Denormalization Mistakes

Recommended Test Cases

Best Practices

Practice Exercise

Normalize an online learning-platform enrollment table and design one justified denormalized read model.

Starting Relation

COURSE_ACTIVITY

learner_id
learner_name
learner_email
course_id
course_title
category_id
category_name
instructor_ids
instructor_names
chapter_id
chapter_title
lesson_id
lesson_title
enrollment_date
completion_percentage
latest_quiz_score

Requirements

1. Identify candidate keys and functional dependencies.
2. Remove repeating instructor values.
3. Create separate learner, course, category, instructor, chapter, and lesson entities.
4. Create an enrollment associative entity.
5. Create a course-instructor junction table.
6. Define chapter-to-course and lesson-to-chapter relationships.
7. Create a quiz-attempt table that preserves attempt history.
8. Verify First, Second, and Third Normal Forms.
9. Define primary, foreign, unique, and check constraints.
10. Write a query returning learner course progress.
11. Measure the query using representative data.
12. Create a course-progress read model only if justified.
13. Define the normalized source of truth.
14. Define projection synchronization and acceptable staleness.
15. Create a reconciliation query for the read model.

Suggested Normalized Tables

Frequently Asked Questions