Designing Database Schemas

Purpose

Design scalable, maintainable database schemas that balance normalization with query performance:

• Apply normalization principles (1NF-BCNF) appropriately
• Choose optimal data types and constraints
• Design efficient indexing strategies
• Implement common patterns (audit trails, soft deletes, multi-tenancy)
• Create clear entity relationships

Quick Start

-- Well-designed table with proper constraints
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  status VARCHAR(20) NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'suspended', 'deleted')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMPTZ  -- Soft delete
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status) WHERE deleted_at IS NULL;

Features

Feature	Description	Pattern
Normalization	Eliminate redundancy while maintaining query efficiency	3NF for OLTP, denormalize for read-heavy
Primary Keys	UUID vs serial, natural vs surrogate keys	UUID for distributed, serial for simple apps
Foreign Keys	Referential integrity with cascade options	CASCADE for owned data, RESTRICT for referenced
Indexes	Query optimization with minimal write overhead	B-tree default, GIN for JSONB/arrays
Constraints	Data integrity at database level	CHECK, UNIQUE, NOT NULL, EXCLUSION
Partitioning	Horizontal scaling for large tables	Range (time), List (category), Hash (even dist)

Common Patterns

Audit Trail Pattern

-- Add to every auditable table
ALTER TABLE orders ADD COLUMN
  created_by UUID REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
  updated_by UUID REFERENCES users(id),
  updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW();

-- Automatic updated_at trigger
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_updated_at
  BEFORE UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION update_updated_at();

Multi-Tenancy (Row-Level)

-- Tenant isolation with RLS
CREATE TABLE projects (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  tenant_id UUID NOT NULL REFERENCES tenants(id),
  name VARCHAR(255) NOT NULL,
  -- Always include tenant_id in indexes
  UNIQUE (tenant_id, name)
);

CREATE INDEX idx_projects_tenant ON projects(tenant_id);

-- Row Level Security
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Polymorphic Associations

-- Option 1: Separate junction tables (recommended)
CREATE TABLE comments (
  id UUID PRIMARY KEY,
  body TEXT NOT NULL,
  author_id UUID REFERENCES users(id)
);

CREATE TABLE post_comments (
  comment_id UUID PRIMARY KEY REFERENCES comments(id),
  post_id UUID NOT NULL REFERENCES posts(id)
);

CREATE TABLE task_comments (
  comment_id UUID PRIMARY KEY REFERENCES comments(id),
  task_id UUID NOT NULL REFERENCES tasks(id)
);

-- Option 2: JSONB for flexible relations (when schema varies)
CREATE TABLE activities (
  id UUID PRIMARY KEY,
  subject_type VARCHAR(50) NOT NULL,
  subject_id UUID NOT NULL,
  metadata JSONB DEFAULT '{}',
  CHECK (subject_type IN ('post', 'task', 'comment'))
);
CREATE INDEX idx_activities_subject ON activities(subject_type, subject_id);

JSONB for Semi-Structured Data

-- Good: Configuration, metadata, varying attributes
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  base_price DECIMAL(10,2) NOT NULL,
  attributes JSONB DEFAULT '{}'  -- Color, size, custom fields
);

CREATE INDEX idx_products_attrs ON products USING GIN (attributes);

-- Query JSONB
SELECT * FROM products
WHERE attributes @> '{"color": "red"}'
  AND (attributes->>'size')::int > 10;

Use Cases

• Greenfield database design for new applications
• Schema reviews and optimization recommendations
• Migration from NoSQL to relational or vice versa
• Multi-tenant SaaS database architecture
• Audit and compliance requirements implementation

Best Practices

Do	Avoid
Use UUID for distributed systems, serial for simple apps	Auto-incrementing IDs exposed to users (enumeration risk)
Apply 3NF for OLTP, denormalize strategically for reads	Over-normalizing lookup tables (country codes, etc.)
Create indexes matching query WHERE/ORDER BY patterns	Indexing every column (write performance penalty)
Use CHECK constraints for enum-like values	Storing booleans as strings or integers
Add NOT NULL unless truly optional	Nullable columns without clear semantics
Prefix indexes with table name: idx_users_email	Generic index names like index1
Use TIMESTAMPTZ for all timestamps	Storing timestamps without timezone
Design for the 80% use case first	Premature optimization for edge cases

Schema Review Checklist

[ ] All tables have primary keys
[ ] Foreign keys have appropriate ON DELETE actions
[ ] Indexes exist for all foreign keys
[ ] Indexes match common query patterns
[ ] No nullable columns without clear use case
[ ] Timestamps use TIMESTAMPTZ
[ ] Audit columns (created_at, updated_at) present
[ ] Naming follows consistent convention
[ ] JSONB used only for truly variable schema
[ ] Partitioning considered for tables > 10M rows

Related Skills

See also these related skill documents:

• managing-database-migrations - Safe schema evolution patterns
• optimizing-databases - Query and index optimization
• building-with-supabase - PostgreSQL with RLS patterns