schema-design-guide

Schema Design Guide

Design well-structured relational database schemas with proper normalization, strategic indexing, referential integrity, and clear naming conventions.

When to Use

• User designs new database tables
• User asks about normalization or denormalization
• User needs help with indexing strategy
• User defines relationships between tables
• User asks about naming conventions for database objects

Core Patterns

Normalization (1NF through 3NF)

Apply normalization to eliminate redundancy, then selectively denormalize for performance.

-- BAD: Unnormalized (repeating groups, redundant data)
CREATE TABLE orders_bad (
  id SERIAL PRIMARY KEY,
  customer_name VARCHAR(255),
  customer_email VARCHAR(255),
  product1_name VARCHAR(255),
  product1_price DECIMAL(10,2),
  product2_name VARCHAR(255),
  product2_price DECIMAL(10,2)
);

-- GOOD: Normalized to 3NF
CREATE TABLE customers (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE products (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  status VARCHAR(50) NOT NULL DEFAULT 'pending'
    CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
  ordered_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity INT NOT NULL CHECK (quantity > 0),
  unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
  UNIQUE (order_id, product_id)
);

Indexing Strategy

Index columns used in WHERE, JOIN, and ORDER BY clauses. Prefer composite indexes that match query patterns.

-- Index for frequent queries
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
CREATE INDEX idx_orders_status_ordered_at ON orders(status, ordered_at DESC);
CREATE INDEX idx_order_items_order_id ON order_items(order_id);

-- Partial index for active records only
CREATE INDEX idx_orders_pending ON orders(ordered_at DESC)
  WHERE status = 'pending';

-- Covering index to avoid table lookups
CREATE INDEX idx_products_name_price ON products(name, price);

-- Expression index for case-insensitive search
CREATE INDEX idx_customers_email_lower ON customers(LOWER(email));

-- GIN index for full-text search
CREATE INDEX idx_products_name_search ON products USING gin(to_tsvector('english', name));

Constraints and Data Integrity

Use database-level constraints as the last line of defense. Application validation can have bugs; constraints cannot be bypassed.

CREATE TABLE accounts (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email VARCHAR(255) NOT NULL,
  balance DECIMAL(12,2) NOT NULL DEFAULT 0 CHECK (balance >= 0),
  account_type VARCHAR(20) NOT NULL CHECK (account_type IN ('checking', 'savings')),
  opened_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  closed_at TIMESTAMPTZ,

  -- Ensure closed_at is after opened_at
  CONSTRAINT valid_dates CHECK (closed_at IS NULL OR closed_at > opened_at),

  -- Unique email per account type
  CONSTRAINT unique_email_per_type UNIQUE (email, account_type)
);

-- Foreign key with appropriate action
CREATE TABLE transactions (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  account_id BIGINT NOT NULL REFERENCES accounts(id) ON DELETE RESTRICT,
  amount DECIMAL(12,2) NOT NULL,
  description TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Naming Conventions

Consistent naming makes schemas self-documenting.

-- Tables: plural, snake_case
CREATE TABLE user_profiles (...);
CREATE TABLE order_items (...);

-- Columns: singular, snake_case
-- Primary key: id
-- Foreign key: referenced_table_singular_id
CREATE TABLE comments (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  user_id BIGINT NOT NULL REFERENCES users(id),
  post_id BIGINT NOT NULL REFERENCES posts(id),
  body TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Indexes: idx_table_columns
CREATE INDEX idx_comments_user_id ON comments(user_id);
CREATE INDEX idx_comments_post_id_created_at ON comments(post_id, created_at DESC);

-- Constraints: chk_table_description, uq_table_columns
ALTER TABLE comments ADD CONSTRAINT chk_comments_body_length CHECK (length(body) > 0);

Strategic Denormalization

Denormalize only when you have measured performance problems and normalization is the bottleneck.

-- Denormalized: Store computed totals for read-heavy dashboards
CREATE TABLE order_summaries (
  order_id BIGINT PRIMARY KEY REFERENCES orders(id),
  item_count INT NOT NULL DEFAULT 0,
  total_amount DECIMAL(12,2) NOT NULL DEFAULT 0,
  updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Keep denormalized data in sync with a trigger
CREATE OR REPLACE FUNCTION update_order_summary()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO order_summaries (order_id, item_count, total_amount, updated_at)
  SELECT
    COALESCE(NEW.order_id, OLD.order_id),
    COUNT(*),
    COALESCE(SUM(quantity * unit_price), 0),
    now()
  FROM order_items
  WHERE order_id = COALESCE(NEW.order_id, OLD.order_id)
  ON CONFLICT (order_id)
  DO UPDATE SET
    item_count = EXCLUDED.item_count,
    total_amount = EXCLUDED.total_amount,
    updated_at = EXCLUDED.updated_at;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_order_items_summary
AFTER INSERT OR UPDATE OR DELETE ON order_items
FOR EACH ROW EXECUTE FUNCTION update_order_summary();

Anti-Patterns

• No primary key: Every table must have a primary key. Without one, you cannot reliably update or delete specific rows.
• Using VARCHAR for everything: Use appropriate types -- INT for counts, DECIMAL for money, TIMESTAMPTZ for timestamps, BOOLEAN for flags.
• Missing foreign keys: Skipping foreign keys "for performance" leads to orphaned records and data corruption. The database should enforce referential integrity.
• Over-indexing: Every index slows writes and uses storage. Only index columns that appear in frequent query WHERE, JOIN, or ORDER BY clauses.
• Premature denormalization: Start normalized. Denormalize only when query performance is measurably insufficient and indexing cannot help.
• Using soft deletes everywhere: deleted_at columns add complexity to every query. Use them only when audit history is a real requirement.

Quick Reference

Normal Form	Rule	Fix
1NF	No repeating groups	One value per cell, separate table for lists
2NF	No partial dependencies	Every non-key column depends on the full PK
3NF	No transitive dependencies	Non-key columns depend only on the PK

Index Type	Use Case
B-tree (default)	Equality, range, ORDER BY
Hash	Equality only (rare)
GIN	Full-text, JSONB, arrays
GiST	Geometric, range types
Partial	Queries filtering on a specific value