Database normalization

Backend Fundamentals · Databases · 8 min read

1. What problem does this solve?

Motivation
When you start a backend project you often model data in a single table or a few loosely‑structured collections. As the application grows, that naive design quickly leads to:

• Data duplication – the same customer name, address, or product description stored in many rows.
• Inconsistent updates – changing a customer’s address in one place but forgetting another.
• Wasted storage – repeated strings or blobs take up disk space and slow backups.
• Harder queries – you need JOINs or manual de‑duplication logic in code.

Why it matters

• Correctness – duplicated data can diverge, causing bugs that are hard to trace.
• Performance – larger tables mean slower scans, larger indexes, and more I/O.
• Maintainability – schema changes ripple through many places, increasing regression risk.
• Compliance – GDPR, HIPAA, etc. require you to control data copies and audit changes.

What happens if you ignore normalization?

• Anomalies: insert, update, delete anomalies that corrupt data.
• Scalability limits: as rows grow, queries become slower; replication lag increases.
• Operational headaches: backups, restores, and migrations become fragile.

Where it appears in real backends

• E‑commerce: customers, orders, products, shipping addresses.
• Social media: users, posts, comments, likes.
• SaaS: tenants, users, subscriptions, usage metrics.
• Microservices: shared reference data (countries, currencies) often stored in a normalized table.

2. Mental model

Analogy

Think of a library. If every book had its own copy of the author’s biography, the library would store the same text thousands of times. Instead, the library keeps one biography per author and references it from each book. This reduces space and ensures that updating the biography updates all books automatically.

Engineering model

• Entities → tables (or collections) that hold a single concept (e.g., Customer, Order).
• Attributes → columns that describe the entity.
• Relationships → foreign keys or references that link entities.
• Normalization → a set of rules that reorganize tables so that each piece of information appears only once, and relationships are explicit.

3. Deep explanation

4. How it works

1. Identify entities – list the real‑world objects you need to store.
2. Define attributes – decide what data each entity holds.
3. Determine primary keys – choose a unique identifier for each entity.
4. Apply functional dependencies – find columns that depend on others.
5. Apply normal forms – iteratively split tables to satisfy 1NF → 2NF → 3NF → BCNF.
6. Add foreign keys – link related tables.
7. Create indexes – on foreign keys and frequently queried columns.
8. Test with sample data – run insert/update/delete to ensure no anomalies.

sequenceDiagram
    participant App
    participant DB
    App->>DB: INSERT INTO customers(id, name, email)
    DB->>DB: Insert row
    App->>DB: INSERT INTO orders(id, customer_id, total)
    DB->>DB: Insert row
    App->>DB: SELECT c.name, o.total FROM customers c JOIN orders o ON c.id = o.customer_id
    DB->>App: Return joined rows

5. Practical implementation

Example: Customer & Order tables in PostgreSQL

-- customers table
CREATE TABLE customers (
    id          BIGSERIAL PRIMARY KEY,
    name        TEXT NOT NULL,
    email       TEXT UNIQUE NOT NULL,
    created_at  TIMESTAMP WITH TIME ZONE DEFAULT now()
);

-- orders table
CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    total       NUMERIC(10,2) NOT NULL,
    status      TEXT NOT NULL,
    created_at  TIMESTAMP WITH TIME ZONE DEFAULT now()
);

TypeScript/Node.js with Prisma

// prisma/schema.prisma
model Customer {
  id        BigInt   @id @default(autoincrement())
  name      String
  email     String   @unique
  orders    Order[]
  createdAt DateTime @default(now())
}

model Order {
  id         BigInt   @id @default(autoincrement())
  customer   Customer @relation(fields: [customerId], references: [id])
  customerId BigInt
  total      Decimal
  status     String
  createdAt  DateTime @default(now())
}

// src/services/orderService.ts
import { prisma } from '../prismaClient';

export async function createOrder(customerId: number, total: number) {
  // Transaction ensures consistency across tables
  return await prisma.$transaction(async (tx) => {
    const order = await tx.order.create({
      data: { customerId, total, status: 'PENDING' },
    });
    // Example: update customer's last order date
    await tx.customer.update({
      where: { id: customerId },
      data: { lastOrderAt: new Date() },
    });
    return order;
  });
}

Comments

• prisma.$transaction guarantees that either both inserts succeed or both fail.
• The foreign key customerId enforces referential integrity at the DB level.

6. Production considerations

7. Common mistakes

8. Senior engineer perspective

What changes at scale?

• Architecture decisions

  • Choose a single source of truth for reference data (e.g., countries, currencies).
  • Decide between schema‑on‑write (strict normalization) vs schema‑on‑read (denormalized views).
  • Use materialized views or cached aggregates for heavy read workloads.

• Trade‑offs

  • Normalization reduces write amplification but can increase read latency due to joins.
  • Denormalization can double storage but speeds up reads; acceptable if writes are rare.

• Bottlenecks

  • Join storms on hot tables.
  • Lock contention on highly concurrent writes.
  • Index bloat on large tables.

• Failure scenarios

  • Transaction deadlocks: use consistent lock ordering.
  • Schema drift: enforce migration pipelines and versioned APIs.

• Monitoring

  • Track query times, lock wait times, and index usage.
  • Alert on high SELECT latency on join queries.

• Cost/performance

  • Normalized tables reduce storage costs but may require more compute for joins.
  • Denormalized tables can reduce compute but increase storage and backup costs.

• When NOT to use

  • If the workload is write‑heavy and simple (e.g., logging), a flat table may suffice.
  • If you need real‑time analytics on large fact tables, consider a data warehouse (Snowflake, BigQuery) rather than a normalized OLTP DB.

9. Interview questions

10. Hands‑on task

Task:
Add a CustomerAddress table to the existing schema and refactor the orders table to reference it via a foreign key instead of storing the address inline.

Steps:

1. Create a new customer_addresses table with columns: id, customer_id, street, city, state, zip.
2. Add a foreign key address_id to orders.
3. Write a migration script (Prisma or raw SQL) to populate customer_addresses from existing orders.
4. Update the createOrder service to accept an addressId instead of address fields.
5. Write a unit test that verifies an order’s address is correctly linked and that updating a customer address propagates to all orders.

11. Quick revision

• Normalization removes data duplication and update anomalies.
• 1NF: atomic columns, no repeating groups.
• 2NF: no partial dependencies on composite keys.
• 3NF: no transitive dependencies.
• BCNF: every determinant is a candidate key.
• Primary key → unique identifier; foreign key → reference.
• Use transactions to keep multi‑table writes atomic.
• Index foreign keys for join performance.
• Denormalization trades consistency for speed; use sparingly.
• Monitor join latency; consider materialized views or caching.
• Always enforce referential integrity with FK constraints.
• Keep migrations idempotent and versioned.
• In NoSQL, embedding is the analog of denormalization.

12. References

• PostgreSQL Documentation – Normalization
• MySQL Documentation – Normalization
• Prisma Schema Language
• SQL:2003 Standard – Normal Forms
• Database Normalization Guide – Microsoft Docs
• NoSQL vs Relational: When to Normalize