database-design

Database Design Skill — Schema & Query Expert

This skill provides comprehensive database design guidance for building reliable, performant data layers.

Schema Design Principles

Normalization Levels

• 1NF: No repeating groups, atomic values
• 2NF: 1NF + no partial dependencies
• 3NF: 2NF + no transitive dependencies
• Denormalize for read performance when justified by measurement

Entity Template

CREATE TABLE entities (
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  -- Business fields
  name        VARCHAR(100) NOT NULL,
  code        VARCHAR(20) NOT NULL UNIQUE,
  status      VARCHAR(20) NOT NULL DEFAULT 'active'
    CHECK (status IN ('active', 'inactive', 'archived')),
  -- JSON for flexible metadata
  metadata    JSONB DEFAULT '{}',
  -- Audit fields
  created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at  TIMESTAMPTZ,  -- Soft delete
  created_by  UUID NOT NULL REFERENCES users(id),
  updated_by  UUID NOT NULL REFERENCES users(id)
);

-- Essential indexes
CREATE INDEX idx_entities_status ON entities(status) WHERE deleted_at IS NULL;
CREATE INDEX idx_entities_code ON entities(code);
CREATE INDEX idx_entities_created_at ON entities(created_at DESC);

Migration Safety Rules

Every Migration Must Have

-- UP Migration
-- Description: [What this migration does]
-- Reversible: YES
-- Data Loss: NONE
-- Estimated Duration: [expected runtime]

BEGIN;
  -- Schema changes here
  -- Data migrations here
COMMIT;

-- DOWN Migration (rollback)
BEGIN;
  -- Exact reverse of UP
COMMIT;

Migration Checklist

• Both UP and DOWN migrations written
• No data loss in UP migration
• DOWN migration actually reverses changes
• Tested with production-like data volume
• Foreign key constraints added AFTER data migration
• Indexes created CONCURRENTLY (PostgreSQL)

Query Optimization

Index Strategy Decision Tree

Is it a PK? → Auto-indexed
Is it a FK? → Always index
Used in WHERE? → Index it
Used in ORDER BY? → Consider index
Used in both WHERE + ORDER BY? → Composite index
High cardinality? → B-tree index
Low cardinality? → Consider partial index
Text search? → GIN/GiST index