Designing Database Schemas

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

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

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

Converted and distributed by TomeVault — claim your Tome and manage your conversions.