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

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 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.

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)
);

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()
);

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.

What About BCNF, 4NF and 5NF?

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

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.

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. 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 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.