# Postgres Normalization Guide: 1NF, 2NF, and 3NF Explained with Real Examples

> Understand Postgres database normalization from first to third normal form with practical schema examples — and learn when to denormalize deliberately.

- **Published:** 2025-12-13
- **Author:** Clixo
- **Reading time:** 6 min read
- **Tags:** postgres, normalization, schema-design, beginners, database
- **Canonical URL:** https://clixo.sh/blog/postgres-normalization-guide-1nf-2nf-3nf

Every Postgres schema starts somewhere. If you skip normalization and let tables grow organically, you end up with data that is duplicated across rows, updates that have to touch multiple places to stay consistent, and queries that return wrong results because the same fact is stored in two different ways. Normalization is the discipline that prevents this. It is not theoretical — it is the practical foundation of a schema you can trust.

This guide explains the first three normal forms with concrete examples and tells you when it is acceptable to break them.

## Why Normalization Matters

Without normalization rules, a table might store the same data in multiple rows. A customer's email stored on every order row means changing that email requires updating every order, and missing one leaves the data inconsistent. Normalization structures data so that each fact lives in exactly one place.

The three normal forms build on each other. You cannot be in second normal form without also being in first.

## First Normal Form (1NF): Atomic Values and No Repeating Groups

A table is in first normal form when:

- Every column contains atomic (indivisible) values
- Each row is uniquely identified by a primary key
- There are no repeating groups (no multiple columns storing the same kind of data)

**Violation example:**

```
| order_id | customer_name | product_1 | product_2 | product_3 |
|----------|---------------|-----------|-----------|-----------|
| 1        | Alice         | Widget    | Gadget    | NULL      |
```

The `product_1`, `product_2`, `product_3` columns are a repeating group. This breaks 1NF.

**1NF-compliant version:**

```
orders:
| order_id | customer_name |
|----------|---------------|
| 1        | Alice         |

order_items:
| item_id | order_id | product_name |
|---------|----------|--------------|
| 1       | 1        | Widget       |
| 2       | 1        | Gadget       |
```

Now each fact is in one place and each row is uniquely identified.

Another common 1NF violation is storing multiple values in a single cell:

```
| user_id | phone_numbers       |
|---------|---------------------|
| 1       | 555-1234, 555-5678  |
```

A comma-separated list in a single column is not atomic. Normalize it to a separate table or, if the use case genuinely calls for it, use a Postgres array — but understand that arrays are harder to query and join than a proper table.

## Second Normal Form (2NF): No Partial Dependencies

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

This only applies to tables with composite primary keys. If a table has a single-column primary key, it is automatically in 2NF.

**Violation example** — an `order_items` table with a composite key of `(order_id, product_id)`:

```
| order_id | product_id | quantity | product_name | product_category |
|----------|------------|----------|--------------|------------------|
| 1        | 10         | 2        | Widget       | Tools            |
| 2        | 10         | 1        | Widget       | Tools            |
```

`product_name` and `product_category` depend only on `product_id`, not on the combination of `order_id` and `product_id`. They are partial dependencies — a 2NF violation.

**2NF-compliant version:**

```
order_items:
| order_id | product_id | quantity |
|----------|------------|----------|
| 1        | 10         | 2        |
| 2        | 10         | 1        |

products:
| product_id | product_name | product_category |
|------------|--------------|------------------|
| 10         | Widget       | Tools            |
```

Product information lives in one place. Changing a product name requires one update, not one per order.

## Third Normal Form (3NF): No Transitive Dependencies

A table is in third normal form when it is in 2NF and no non-key column depends on another non-key column.

**Violation example:**

```
| employee_id | department_id | department_name | department_budget |
|-------------|---------------|-----------------|-------------------|
| 1           | 5             | Engineering     | 500000            |
| 2           | 5             | Engineering     | 500000            |
```

`department_name` and `department_budget` depend on `department_id`, not on `employee_id`. They have a transitive dependency through `department_id`.

**3NF-compliant version:**

```
employees:
| employee_id | department_id |
|-------------|---------------|
| 1           | 5             |
| 2           | 5             |

departments:
| department_id | department_name | department_budget |
|---------------|-----------------|-------------------|
| 5             | Engineering     | 500000            |
```

Now changing the department budget is a single-row update.

## Implementing Normalized Schemas in Postgres

Third Normal Form translates directly into Postgres:

```sql
CREATE TABLE departments (
  id     BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name   TEXT NOT NULL,
  budget NUMERIC(15, 2) NOT NULL
);

CREATE TABLE employees (
  id            BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  department_id BIGINT NOT NULL REFERENCES departments(id),
  name          TEXT NOT NULL
);

CREATE INDEX ON employees (department_id);
```

Foreign keys enforce the relationships at the database level. Without `REFERENCES`, application code becomes the only thing preventing orphaned records.

```mermaid
erDiagram
  DEPARTMENTS {
    bigint id PK
    text name
    numeric budget
  }
  EMPLOYEES {
    bigint id PK
    bigint department_id FK
    text name
  }
  ORDERS {
    bigint id PK
    timestamptz created_at
  }
  ORDER_ITEMS {
    bigint id PK
    bigint order_id FK
    bigint product_id FK
    int quantity
  }
  PRODUCTS {
    bigint id PK
    text name
    text category
  }
  DEPARTMENTS ||--o{ EMPLOYEES : employs
  ORDERS ||--o{ ORDER_ITEMS : contains
  PRODUCTS ||--o{ ORDER_ITEMS : "included in"
```

## When to Denormalize Deliberately

3NF is the right default. The cases where breaking it is justified are narrow but real:

**Read-heavy reporting queries:** Joining five tables to assemble a dashboard row can be slow. A denormalized summary table or materialized view that pre-computes the join is legitimate when you have profiled and confirmed the join is the bottleneck.

**Event sourcing and audit logs:** Audit records should capture the state at the time of the event, not reference live data that will change. Storing `customer_name` on an audit log row is intentional denormalization.

**Search and full-text indexing:** A separate search-optimized store that duplicates data from multiple tables is an accepted pattern. The source of truth remains normalized; the search index is a derived projection.

The rule: normalize first, denormalize later with a specific measured reason. Schemas that start denormalized are far harder to reason about and far more likely to contain inconsistent data.

---

If you are designing a schema for a new product and want a second set of eyes on the data model before you build, Clixo can review it with you. [Start a build](https://clixo.sh/#contact) and we can work through the tradeoffs together.

---

Clixo · 1141 W Bryn Mawr Ave, Itasca, IL 60143, US · [hello@clixo.sh](mailto:hello@clixo.sh)
[Start a build](https://clixo.sh/#contact) · [All services](https://clixo.sh/services) · [Agent guide (llms.txt)](https://clixo.sh/llms.txt)