database-design

Database Design

A practical reference for designing, indexing, migrating, and operating relational databases in production, with PostgreSQL as the primary target.

When to Activate

• Designing a new database schema or data model
• Adding indexes to improve query performance
• Writing or reviewing a database migration
• Optimizing a slow SQL query
• Choosing between normalization and denormalization strategies
• Setting up connection pooling for a service
• Planning a zero-downtime schema change in production

---

Schema Design Principles

Normalization

First Normal Form (1NF) — atomic values, no repeating groups.

-- BAD: repeating groups in a single column
CREATE TABLE orders (
    id        BIGSERIAL PRIMARY KEY,
    user_id   BIGINT NOT NULL,
    item_ids  TEXT NOT NULL  -- "1,2,3" stored as a string
);

-- GOOD: each value gets its own row
CREATE TABLE orders (
    id      BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL
);

CREATE TABLE order_items (
    id         BIGSERIAL PRIMARY KEY,
    order_id   BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(id)
);

Second Normal Form (2NF) — no partial dependencies on a composite key. Every non-key column must depend on the whole key, not just part of it.

-- BAD: composite key (order_id, product_id), but product_name depends only on product_id
CREATE TABLE order_items (
    order_id     BIGINT NOT NULL,
    product_id   BIGINT NOT NULL,
    product_name TEXT NOT NULL,   -- partial dependency: only on product_id
    quantity     INT  NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- GOOD: move product_name to the products table
CREATE TABLE products (
    id   BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE order_items (
    order_id   BIGINT NOT NULL,
    product_id BIGINT NOT NULL REFERENCES products(id),
    quantity   INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Third Normal Form (3NF) — no transitive dependencies. Non-key columns must not depend on other non-key columns.