Database Design Patterns in 2026: Normalization, Denormalization, and When Each Applies

Database schema decisions made in week one live with you for years. A poorly designed schema doesn't fail immediately — it degrades slowly: queries get slower, the ORM produces n+1 nightmares, migrations become terrifying, and adding a new feature requires touching fifteen tables.

Good schema design isn't about following normalization rules dogmatically. It's about understanding the access patterns, write vs. read ratio, consistency requirements, and growth trajectory of your data — then choosing the right tradeoffs deliberately.

This post covers the core patterns: normalization vs. denormalization, how to handle polymorphism cleanly, when JSONB is appropriate, and the EAV anti-pattern that keeps showing up and should be avoided.

---

Normalization vs. Denormalization: The Tradeoff Map

---

Core Schema: Third Normal Form (3NF) as Baseline

Start normalized. Denormalize only when profiling proves it necessary.

-- E-commerce schema: clean 3NF
CREATE TABLE customers (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email       TEXT NOT NULL UNIQUE,
    name        TEXT NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE products (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sku         TEXT NOT NULL UNIQUE,
    name        TEXT NOT NULL,
    base_price  NUMERIC(10, 2) NOT NULL CHECK (base_price >= 0),
    category_id UUID NOT NULL REFERENCES categories(id),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id     UUID NOT NULL REFERENCES customers(id),
    status          TEXT NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending','confirmed','shipped','delivered','cancelled')),
    subtotal        NUMERIC(10, 2) NOT NULL,
    tax             NUMERIC(10, 2) NOT NULL DEFAULT 0,
    total           NUMERIC(10, 2) GENERATED ALWAYS AS (subtotal + tax) STORED,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id    UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id  UUID NOT NULL REFERENCES products(id),
    quantity    INTEGER NOT NULL CHECK (quantity > 0),
    unit_price  NUMERIC(10, 2) NOT NULL,  -- Snapshot price at time of order
    line_total  NUMERIC(10, 2) GENERATED ALWAYS AS (quantity * unit_price) STORED
);

-- Indexes for common access patterns
CREATE INDEX idx_orders_customer ON orders (customer_id, created_at DESC);
CREATE INDEX idx_order_items_order ON order_items (order_id);
CREATE INDEX idx_products_category ON products (category_id);

Note: unit_price on order_items is intentionally denormalized from products.base_price. This is correct — order history must be immutable. If a product's price changes, past orders should not be affected.

---

Denormalization Pattern: Materialized Aggregates

When aggregate queries are too slow, maintain them:

-- Denormalized customer stats: avoid COUNT(*) on orders every time
CREATE TABLE customer_stats (
    customer_id     UUID PRIMARY KEY REFERENCES customers(id),
    order_count     INTEGER NOT NULL DEFAULT 0,
    total_spent     NUMERIC(12, 2) NOT NULL DEFAULT 0,
    last_order_at   TIMESTAMPTZ,
    updated_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Trigger to maintain stats on order creation
CREATE OR REPLACE FUNCTION update_customer_stats()
RETURNS TRIGGER LANGUAGE plpgsql AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO customer_stats (customer_id, order_count, total_spent, last_order_at)
        VALUES (NEW.customer_id, 1, NEW.total, NEW.created_at)
        ON CONFLICT (customer_id) DO UPDATE SET
            order_count   = customer_stats.order_count + 1,
            total_spent   = customer_stats.total_spent + EXCLUDED.total_spent,
            last_order_at = GREATEST(customer_stats.last_order_at, EXCLUDED.last_order_at),
            updated_at    = now();
    END IF;
    RETURN NULL;
END;
$$;

CREATE TRIGGER trg_update_customer_stats
AFTER INSERT ON orders
FOR EACH ROW EXECUTE FUNCTION update_customer_stats();

Now SELECT * FROM customer_stats WHERE customer_id = $1 is O(1) regardless of order history length.

---

Polymorphic Associations: Three Patterns

When multiple tables need to share a relationship (comments on posts AND comments on products), there are three approaches:

Pattern A: Separate Tables (Best for Strict Typing)

-- Each entity gets its own comments table
CREATE TABLE post_comments (
    id         UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    post_id    UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    author_id  UUID NOT NULL REFERENCES users(id),
    body       TEXT NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE product_comments (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id  UUID NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    author_id   UUID NOT NULL REFERENCES users(id),
    body        TEXT NOT NULL,
    rating      SMALLINT CHECK (rating BETWEEN 1 AND 5),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

✅ FK integrity maintained
✅ Clean schema per entity
❌ Schema changes needed for each new commentable type

Pattern B: Polymorphic FK (Common ORM Default — Use Carefully)

CREATE TABLE comments (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    commentable_type TEXT NOT NULL,  -- 'Post', 'Product', 'Order'
    commentable_id   UUID NOT NULL,
    author_id        UUID NOT NULL REFERENCES users(id),
    body             TEXT NOT NULL,
    created_at       TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Cannot use a foreign key — must enforce in application layer
CREATE INDEX idx_comments_poly ON comments (commentable_type, commentable_id);

✅ Works for any entity type without schema changes
❌ No foreign key constraint — orphaned rows possible
❌ No cascade deletes — must clean up manually
Use only when the entity count is large and FK enforcement would be prohibitive.

Pattern C: Shared Base Table (Best for Strong Consistency)

-- Abstract base for all commentable entities
CREATE TABLE commentables (
    id      UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    type    TEXT NOT NULL  -- 'post', 'product', 'order'
);

CREATE TABLE posts (
    id      UUID PRIMARY KEY REFERENCES commentables(id),
    title   TEXT NOT NULL,
    body    TEXT NOT NULL
);

CREATE TABLE products (
    id          UUID PRIMARY KEY REFERENCES commentables(id),
    name        TEXT NOT NULL,
    base_price  NUMERIC(10, 2) NOT NULL
);

CREATE TABLE comments (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    commentable_id  UUID NOT NULL REFERENCES commentables(id) ON DELETE CASCADE,
    author_id       UUID NOT NULL REFERENCES users(id),
    body            TEXT NOT NULL,
    created_at      TIMESTAMPTZ NOT NULL DEFAULT now()
);

✅ FK integrity via commentables
✅ Cascade delete works
❌ Extra join to get the concrete entity

---

JSONB: Flexible Schema Without Chaos

PostgreSQL's JSONB is not a replacement for relational schema — it's a tool for genuinely variable data.

When JSONB is Appropriate

-- Product variants: different products have different attributes
-- Electronics: { "storage": "256GB", "color": "space_gray", "connectivity": "5G" }
-- Clothing: { "size": "M", "color": "navy", "material": "cotton" }
-- Books: { "isbn": "978-...", "author": "...", "pages": 320 }

CREATE TABLE products (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    category_id UUID NOT NULL REFERENCES categories(id),
    base_price  NUMERIC(10, 2) NOT NULL,
    attributes  JSONB NOT NULL DEFAULT '{}'  -- Category-specific attributes
);

-- GIN index for attribute searches
CREATE INDEX idx_products_attributes ON products USING GIN (attributes);

-- Query: find all blue cotton shirts in size M
SELECT * FROM products
WHERE
    category_id = 'clothing-uuid'
    AND attributes @> '{"color": "navy", "size": "M", "material": "cotton"}';

-- Extract scalar value
SELECT
    name,
    (attributes->>'storage') AS storage_size,
    base_price
FROM products
WHERE
    category_id = 'electronics-uuid'
    AND (attributes->>'storage') = '256GB';

JSONB Schema Validation with Check Constraint

-- Ensure required fields are present in JSONB
CREATE OR REPLACE FUNCTION validate_product_attributes(
    category_slug TEXT,
    attrs JSONB
) RETURNS BOOLEAN LANGUAGE plpgsql AS $$
BEGIN
    CASE category_slug
        WHEN 'electronics' THEN
            RETURN attrs ? 'storage' AND attrs ? 'color';
        WHEN 'clothing' THEN
            RETURN attrs ? 'size' AND attrs ? 'color';
        ELSE
            RETURN TRUE;
    END CASE;
END;
$$;

When NOT to Use JSONB

-- ❌ Bad: status as JSONB when it's always the same fields
-- This should be proper columns
CREATE TABLE users (
    id      UUID PRIMARY KEY,
    meta    JSONB  -- { "email_verified": true, "phone": "...", "country": "US" }
);

-- ✅ Good: proper columns for known fields
CREATE TABLE users (
    id              UUID PRIMARY KEY,
    email_verified  BOOLEAN NOT NULL DEFAULT FALSE,
    phone           TEXT,
    country         CHAR(2)
);

Rule: If you always need a field, it should be a column (typed, indexed, constrained). JSONB is for data where the shape varies per row.

---

The EAV Anti-Pattern (Avoid)

Entity-Attribute-Value (EAV) is the worst pattern in relational databases. It looks like flexibility; it's actually a query nightmare.

-- ❌ EAV: Don't do this
CREATE TABLE product_attributes (
    product_id      UUID NOT NULL REFERENCES products(id),
    attribute_name  TEXT NOT NULL,
    attribute_value TEXT NOT NULL,  -- Everything is a string — lose type safety
    PRIMARY KEY (product_id, attribute_name)
);

-- Querying EAV is painful
SELECT p.name, pa_color.attribute_value AS color, pa_size.attribute_value AS size
FROM products p
JOIN product_attributes pa_color ON pa_color.product_id = p.id AND pa_color.attribute_name = 'color'
JOIN product_attributes pa_size  ON pa_size.product_id  = p.id AND pa_size.attribute_name  = 'size'
WHERE pa_color.attribute_value = 'navy'
  AND pa_size.attribute_value  = 'M';

-- ✅ Use JSONB instead — same flexibility, proper indexing, cleaner queries
SELECT name, attributes->>'color' AS color, attributes->>'size' AS size
FROM products
WHERE attributes @> '{"color": "navy", "size": "M"}';

EAV forces: string-typed everything, multi-join queries for each attribute, impossible FK constraints, and terrible performance at scale. Replace it with JSONB for flexibility or proper columns for known fields.

---

Temporal Data: Validity Periods

For data that changes over time and you need to query historical state:

-- Pricing history: know what price was active on any date
CREATE TABLE product_pricing (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    product_id  UUID NOT NULL REFERENCES products(id),
    price       NUMERIC(10, 2) NOT NULL,
    valid_from  DATE NOT NULL,
    valid_until DATE,  -- NULL = currently active
    created_by  UUID REFERENCES users(id),

    -- Prevent overlapping periods for same product
    EXCLUDE USING GIST (
        product_id WITH =,
        daterange(valid_from, valid_until, '[)') WITH &&
    )
);

-- Current price
SELECT price FROM product_pricing
WHERE product_id = $1 AND valid_from <= CURRENT_DATE
  AND (valid_until IS NULL OR valid_until > CURRENT_DATE);

-- Price on a specific historical date
SELECT price FROM product_pricing
WHERE product_id = $1 AND valid_from <= $2
  AND (valid_until IS NULL OR valid_until > $2)
ORDER BY valid_from DESC LIMIT 1;

---

Cost and Complexity Reference

---

Working With Viprasol

Our backend team designs and reviews database schemas for SaaS products — catching EAV anti-patterns, designing proper polymorphic associations, and ensuring schemas support future growth without painful migrations.

What we deliver:

• Schema design review and refactoring plan
• PostgreSQL index strategy (covering, partial, GIN, BRIN)
• Migration scripts with zero-downtime expand-contract pattern
• JSONB schema validation and query optimization
• Multi-tenant schema design (row-level, schema-level, database-level)

→ Discuss your database architecture → Software development services

---

See Also

• PostgreSQL Performance Tuning
• Database Migrations with Zero Downtime
• Database Replication and High Availability
• Database Indexing Strategies
• Event Sourcing and CQRS