---
title: "Normalized Database: 1NF, 2NF, 3NF With Real Rows"
description: "A normalized database is one where each fact is stored exactly once, in the table it describes, and tables link to each other by keys."
canonical: "https://www.aifordatabase.com/blog/normalized-database/"
author: "Dhrumil Barot"
author_bio: "Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team."
published: 2026-11-01
updated: 2026-11-01
---

# Normalized Database: 1NF, 2NF, 3NF With Real Rows

![Normalized Database: 1NF, 2NF, 3NF With Real Rows: a hand dropping a tangled pile of thin wires or strings into an open filing cabinet drawer filled with index cards, one drawe](https://cdn.sanity.io/images/upsr56wu/production/5c16b6ffc2c767656779019f538c845f9a56077c-1200x630.png?w=1280&h=672&fit=crop&auto=format)

**TL;DR:** A normalized database is one where each fact is stored exactly once, in the table it describes, and tables link to each other by keys. You get there by applying normal forms in order: 1NF (one value per cell, a primary key), 2NF (no column depends on only part of the key) and 3NF (no column depends on another non-key column). Most production schemas stop at 3NF and denormalize on purpose for analytics.

A normalized database is a relational database designed so that no piece of information is repeated across rows or tables, which prevents update, insert and delete anomalies. Normalization of tables in a database means splitting one wide table into several narrower ones, each about one thing (customers, orders, products), joined by foreign keys. Edgar F. Codd introduced the idea alongside the relational model in 1970 and defined second and third normal form in 1971. "Normalized data" means the same thing at the data level: values that live in one authoritative place.

Below, one messy orders table goes through 1NF, 2NF and 3NF with real rows, and then we look at when to break the rules for reporting.

## Why Does Normalization Matter?

Normalization matters because duplicated data drifts. [Microsoft's Access documentation](https://learn.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description) puts it plainly: when data exists in more than one place, every change "must be changed in exactly the same way in all locations."

A concrete scenario: a SaaS company stores the customer's email on every invoice row. The customer changes their email. Support updates the latest invoice, the billing job reads an older row, and the renewal reminder goes to a dead address. Nothing failed technically; the design allowed two truths.

Unnormalized tables produce three classic anomalies:

*   **Update anomaly:** changing one fact means editing many rows, and missing one leaves contradictions.
*   **Insert anomaly:** you can't record a fact (a new customer) until an unrelated fact (their first order) exists.
*   **Delete anomaly:** deleting the last order for a customer also deletes the only record of that customer.

## The Starting Point: One Messy Table

Here is a typical spreadsheet export turned into a table. Every row is one order.

![An unnormalized orders table repeats customer details and packs two products into one cell](https://cdn.sanity.io/images/upsr56wu/production/78e94894cebcb9d9032f2483cebfef5ef14641c5-1344x410.png?w=1280)

FIG 01 — Ana's email and city are stored twice; order 101 holds two products in one cell.

Problems you can already see: Ana's details appear twice, and `products` holds a list, so "how many lamps did we sell?" needs string parsing.

## What Is First Normal Form (1NF)?

A table is in first normal form when every cell holds a single value, every column holds one data type, there are no repeating groups (like `product1`, `product2` columns or comma lists), and rows are identified by a primary key.

To fix `orders_raw`, give each product its own row. Each product also has a price, so we add it:

```
-- 1NF: one product per row, key = (order_id, product)
CREATE TABLE order_lines_1nf (
  order_id    int,
  product     text,
  unit_price  numeric(10,2),
  qty         int,
  customer    text,
  email       text,
  city        text,
  PRIMARY KEY (order_id, product)
);
```

| order\_id | product | unit\_price | qty | customer | email | city |
| --- | --- | --- | --- | --- | --- | --- |
| 101 | Desk | 250.00 | 1 | Ana | ana@x.io | Pune |
| 101 | Lamp | 40.00 | 2 | Ana | ana@x.io | Pune |
| 102 | Chair | 120.00 | 1 | Ben | ben@y.io | Leeds |
| 103 | Lamp | 40.00 | 1 | Ana | ana@x.io | Pune |

Now every cell is atomic and `SUM(qty) WHERE product = 'Lamp'` returns 3. But Ana's email now appears three times. 1NF is the floor, not the goal.

## What Is Second Normal Form (2NF)?

A table is in second normal form when it is in 1NF and every non-key column depends on the whole primary key, not just part of it.

Our key is `(order_id, product)`. Check each column:

*   `qty` depends on both: how many lamps in order 101. Fine.
*   `unit_price` depends only on `product`. Partial dependency.
*   `customer`, `email`, `city` depend only on `order_id`. Partial dependency.

Fix it by moving each partial dependency into a table keyed by the part it depends on:

```
-- 2NF: split out what depends on only part of the key
CREATE TABLE products (product text PRIMARY KEY, unit_price numeric(10,2));
CREATE TABLE orders   (order_id int PRIMARY KEY, customer text, email text, city text);
CREATE TABLE order_lines (
  order_id int REFERENCES orders,
  product  text REFERENCES products,
  qty      int,
  PRIMARY KEY (order_id, product)
);
```

The lamp price now lives in one row. But `orders` still repeats Ana's email on orders 101 and 103.

## What Is Third Normal Form (3NF)?

A table is in third normal form when it is in 2NF and no non-key column depends on another non-key column (no transitive dependencies). The classic summary: every column depends on "the key, the whole key, and nothing but the key."

In `orders`, `email` and `city` depend on `customer`, and `customer` depends on `order_id`. That chain is the transitive dependency. Pull customers into their own table:

```
-- 3NF: customer facts live once, orders point to them
CREATE TABLE customers (customer_id int PRIMARY KEY, name text, email text UNIQUE, city text);
CREATE TABLE orders (
  order_id    int PRIMARY KEY,
  customer_id int REFERENCES customers,
  ordered_at  timestamptz DEFAULT now()
);
```

![The 3NF schema stores Ana once in customers while orders reference her by id](https://cdn.sanity.io/images/upsr56wu/production/14431d5c8d390eb5e3612acec376e92d7131d4e4-1344x410.png?w=1280)

FIG 02 — Ana's email changes in one row and every order sees it.

Now Ana changes her email with one `UPDATE customers ... WHERE customer_id = 1`, a new customer can exist before ordering, and deleting an order never deletes a customer. That is a normalized database.

VIDEO · [Learn Database Normalization - 1NF, 2NF, 3NF, 4NF, 5NF](https://www.youtube.com/watch?v=GFQaEYEc8_8) (Decomplexify)

## What About BCNF, 4NF and 5NF?

Higher normal forms exist but rarely change a real business schema:

| Normal form | Rule in one line | Defined |
| --- | --- | --- |
| 1NF | Atomic values, a primary key, no repeating groups | Codd, 1970 |
| 2NF | No non-key column depends on part of a composite key | Codd, 1971 |
| 3NF | No non-key column depends on another non-key column | Codd, 1971 |
| BCNF | Every determinant is a candidate key | Boyce and Codd, 1974 |
| 4NF | No independent multi-valued facts in one table | Fagin, 1977 |
| 5NF | Table can't be rebuilt by joining smaller tables | Fagin, 1979 |

A 3NF table that violates BCNF needs overlapping candidate keys, which is uncommon. If you design to 3NF, you are almost always at BCNF too.

## Normalization vs Denormalization: Which Is Better for Analytics?

Normalization is better for the database that runs your app; denormalization is often better for the database that answers questions. They solve different problems.

|  | Normalized (3NF) | Denormalized (star schema, wide tables) |
| --- | --- | --- |
| Best for | Transactions (OLTP): signups, orders, payments | Analytics (OLAP): dashboards, reports |
| Writes | Fast, one row per change | Slower, data copied in many places |
| Reads | Need joins | Few or no joins |
| Integrity | Enforced by keys and constraints | Kept by a pipeline that rebuilds copies |
| Storage | Minimal | Larger |

The common pattern in practice: keep the app database in 3NF, then build denormalized copies for reporting. A data warehouse uses a star schema (one fact table of events or orders, surrounded by wide dimension tables of customers and products), described in Ralph Kimball's dimensional modeling.

## When Should You Denormalize?

Denormalize when a measured read problem costs more than the integrity risk you take on. Good reasons:

*   A dashboard query joins 6+ tables and runs on every page load.
*   You need a historical snapshot (the price at the time of the order, not today's price). Storing `unit_price` on `order_lines` is deliberate, not a mistake.
*   Reporting runs against a separate warehouse or replica that is rebuilt from the normalized source.

In PostgreSQL the safest way to denormalize is a materialized view, which keeps the source normalized and stores a precomputed copy:

```
-- denormalized reporting copy, rebuilt on a schedule
CREATE MATERIALIZED VIEW order_report AS
SELECT o.order_id, o.ordered_at, c.name, c.city, l.product, l.qty, p.unit_price,
       l.qty * p.unit_price AS revenue
FROM orders o
JOIN customers c   ON c.customer_id = o.customer_id
JOIN order_lines l ON l.order_id = o.order_id
JOIN products p    ON p.product = l.product;

CREATE UNIQUE INDEX ON order_report (order_id, product);
REFRESH MATERIALIZED VIEW CONCURRENTLY order_report;
```

`REFRESH ... CONCURRENTLY` lets reads continue during the rebuild but requires a unique index on the view, per the [PostgreSQL documentation](https://www.postgresql.org/docs/current/sql-refreshmaterializedview.html). Bad reasons to denormalize: "joins are slow" without an `EXPLAIN`, or a missing index.

## Pitfalls

*   **Over-normalizing lookups.** Microsoft's guidance notes that splitting cities and ZIP codes into separate tables to remove every dependency "isn't always practical." Normalize what changes.
*   **Confusing duplicates with redundancy.** Two orders with the same product are not redundant; two copies of Ana's email are. If you already have duplicate rows, \[find them with SQL\](/blog/find-duplicates-sql-query/) before adding constraints.
*   **Skipping constraints.** A normalized design without `PRIMARY KEY`, `REFERENCES` and `UNIQUE` still lets bad data in.
*   **Forgetting the reader.** Normalized schemas are harder for non-SQL teammates to query. Reports often need a \[pivot\](/blog/sql-pivot-table/) or a \[running total\](/blog/cumulative-sum-in-sql/) across four joined tables.

That last point is where most teams get stuck: the schema is right, but only engineers can read it. [AI for Database](https://www.aifordatabase.com/) lets anyone ask questions of a normalized PostgreSQL or MySQL database in plain English; it writes the joins, shows the SQL, and can turn the answer into a dashboard that refreshes itself.

D

Written by [Dhrumil Barot](https://www.aifordatabase.com/about/#founder)

Dhrumil is an enterprise analytics expert. Dhrumil built AI for Database so anyone on a team can get answers from live company data in plain English, without waiting on a data team.

[LinkedIn](https://www.linkedin.com/in/bdhrumil)[X](https://x.com/fyidhrumil)

## Frequently Asked Questions

### What is a normalized database?

A relational database where each fact is stored once, in the table it describes, with tables linked by primary and foreign keys. It usually means the schema meets third normal form.

### What does "normalize a table" mean?

To split a table whose columns describe more than one thing into smaller tables, each with its own primary key, so no value repeats across rows. You check 1NF, then 2NF, then 3NF.

### Is a normalized database faster?

For writes, usually yes, because each change touches one row. For reads that need many joins, it can be slower than a denormalized table, which is why analytics often uses star schemas or materialized views.

### How far should I normalize?

Third normal form for application databases. Go to BCNF if you have overlapping candidate keys; 4NF and 5NF problems are rare in business data.

### Is NoSQL data normalized?

Document databases such as MongoDB usually embed related data in one document, which is denormalized by design. You trade update simplicity for single-read access. For a quick syntax refresher on the joins a normalized schema needs, see our \[SQL cheat sheet\](/blog/sql-cheat-sheet/).

Read next

*   [ALIASES IN SQL: COLUMN, TABLE AND SUBQUERY REFERENCE →](https://www.aifordatabase.com/blog/aliases-in-sql/)
*   [8 CALL CENTER METRICS DASHBOARD EXAMPLES (WITH SQL) →](https://www.aifordatabase.com/blog/call-center-metrics-dashboard/)
*   [9 BEST QUERY BUILDERS FOR SQL: VISUAL, CODE, AI (COMPARED) →](https://www.aifordatabase.com/blog/query-builders/)

Source: https://www.aifordatabase.com/blog/normalized-database/