database-schema-designer

name: database-schema-designer description: Design robust, scalable, production-ready database schemas for SQL and NoSQL databases. Use this skill whenever the user mentions designing a schema, creating tables, modeling data, database architecture, normalization, indexing, migrations, or anything about structuring a database — even if they don't use the word "schema". Trigger on phrases like "I need a database for...", "how should I store...", "what tables do I need...", "help me model...", "design my data model", or any request to think through how to persist data. Covers normalization (1NF–3NF), indexing strategies, constraint design, foreign key patterns, soft deletes, audit trails, multi-tenancy, migration patterns, and performance optimization. license: MIT

Database Schema Designer

Design production-ready database schemas with best practices built-in.

Quick Start

Just describe your data model:

design a schema for an e-commerce platform with users, products, orders

What to include in your request:

• Entities and their key relationships
• Scale hints (high-traffic, millions of records, multi-tenant)
• Special requirements (soft deletes, audit trail, multi-tenancy)
• Database preference (SQL/NoSQL, MySQL/PostgreSQL) — defaults to PostgreSQL if unspecified

Process Overview

Requirements → ANALYZE (entities, access patterns, SQL vs NoSQL)
            → DESIGN  (normalize to 3NF, define keys, constraints, types)
            → OPTIMIZE (indexes, denormalize hot paths, add timestamps)
            → MIGRATE  (reversible scripts, backward-compatible, zero-downtime)
            → Production-Ready Schema

Quick Reference

Commands

Core Principles

Anti-Patterns

Common Patterns

Soft Deletes

-- Add to any table that should support soft delete
deleted_at  TIMESTAMP NULL DEFAULT NULL,
deleted_by  BIGINT    NULL REFERENCES users(id)

-- PostgreSQL: partial index keeps queries fast
CREATE INDEX idx_orders_active ON orders(customer_id)
  WHERE deleted_at IS NULL;

-- All queries must filter:
SELECT * FROM orders WHERE deleted_at IS NULL;

Audit Trail

-- Minimal audit columns on every table
created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_by  BIGINT    NOT NULL REFERENCES users(id),
updated_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_by  BIGINT    NULL     REFERENCES users(id)

-- For full change history, use a separate audit table:
CREATE TABLE orders_audit (
  id         BIGINT PRIMARY KEY,
  order_id   BIGINT NOT NULL,
  action     VARCHAR(10) NOT NULL,   -- INSERT / UPDATE / DELETE
  changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  changed_by BIGINT NOT NULL REFERENCES users(id),
  snapshot   JSONB  NOT NULL         -- row state at time of change
);

Multi-Tenancy (Row-Level Isolation)

-- Every tenant-scoped table carries tenant_id
CREATE TABLE projects (
  id         BIGINT PRIMARY KEY,
  tenant_id  BIGINT NOT NULL REFERENCES tenants(id),
  name       VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_projects_tenant (tenant_id)
);

-- Application layer always scopes queries:
SELECT * FROM projects WHERE tenant_id = ? AND deleted_at IS NULL;

-- PostgreSQL: enforce at DB level with Row-Level Security
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.current_tenant')::BIGINT);

Status / State Machine

-- Use ENUM or a lookup table — never multiple boolean columns
status VARCHAR(20) NOT NULL DEFAULT 'pending'
  CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))

-- PostgreSQL native ENUM (rename is painful — prefer VARCHAR + CHECK)
CREATE TYPE order_status AS ENUM ('pending','processing','shipped','delivered','cancelled');

Primary Key Choice

-- Auto-increment (simple, opaque, performant)
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

-- UUID (distributed, globally unique, URL-safe)
id UUID PRIMARY KEY DEFAULT gen_random_uuid()   -- PostgreSQL 13+

-- ULID/KSUID: sortable + unique — best of both worlds (use via app or extension)

Verification Checklist

After designing a schema, confirm:

• Every table has a primary key
• All FK relationships have constraints + ON DELETE strategy
• Indexes on every FK column
• Indexes on frequently queried WHERE/ORDER BY columns
• DECIMAL for monetary values (not FLOAT)
• NOT NULL on required fields; UNIQUE where needed
• CHECK constraints for validation rules
• created_at / updated_at timestamps on every table
• Soft delete strategy defined (deleted_at or hard delete with reason)
• Audit trail considered (who created/changed each record)
• tenant_id on all tenant-scoped tables (SaaS apps)
• Migrations are reversible (UP + DOWN scripts)
• Tested on staging with production-like data

-- 1NF violation → fix with a junction table
-- BAD
CREATE TABLE orders (id INT PRIMARY KEY, product_ids VARCHAR(255));
-- GOOD
CREATE TABLE order_items (
  id INT PRIMARY KEY,
  order_id INT REFERENCES orders(id),
  product_id INT REFERENCES products(id)
);

-- 3NF violation → fix with a lookup table
-- BAD
CREATE TABLE customers (id INT PRIMARY KEY, postal_code VARCHAR(10), country VARCHAR(50));
-- GOOD
CREATE TABLE postal_codes (code VARCHAR(10) PRIMARY KEY, country VARCHAR(50));

created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
-- PostgreSQL
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()

-- JSONB for dynamic/sparse attributes (indexed, fast)
metadata JSONB DEFAULT '{}'
CREATE INDEX idx_meta ON table USING GIN(metadata);

-- Arrays
tags TEXT[] DEFAULT '{}'

-- Ranges
valid_during TSTZRANGE

-- Composite: put most selective column first, match query patterns
CREATE INDEX idx_orders_customer_status ON orders(customer_id, status);
-- Uses: WHERE customer_id = ?  /  WHERE customer_id = ? AND status = ?
-- Misses: WHERE status = ?  (status alone)

-- Partial (PostgreSQL) — smaller, faster for filtered queries
CREATE INDEX idx_orders_pending ON orders(created_at)
  WHERE status = 'pending';

-- Foreign key strategies
FOREIGN KEY (customer_id) REFERENCES customers(id)
  ON DELETE CASCADE     -- delete children with parent (order_items)
  ON DELETE RESTRICT    -- prevent deletion if referenced (safest default)
  ON DELETE SET NULL    -- optional relationships

-- Check constraints
price   DECIMAL(10,2) CHECK (price >= 0)
discount INT           CHECK (discount BETWEEN 0 AND 100)

-- Composite unique
UNIQUE (tenant_id, slug)   -- slug unique per tenant, not globally

-- One-to-Many
CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT
);

-- Many-to-Many (junction table)
CREATE TABLE enrollments (
  student_id  BIGINT REFERENCES students(id) ON DELETE CASCADE,
  course_id   BIGINT REFERENCES courses(id)  ON DELETE CASCADE,
  enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (student_id, course_id)
);

-- Self-referencing (hierarchy)
CREATE TABLE employees (
  id         BIGINT PRIMARY KEY,
  name       VARCHAR(100) NOT NULL,
  manager_id BIGINT REFERENCES employees(id)
);

-- Polymorphic (prefer separate FKs for integrity)
CREATE TABLE comments (
  id       BIGINT PRIMARY KEY,
  content  TEXT NOT NULL,
  post_id  BIGINT REFERENCES posts(id),
  photo_id BIGINT REFERENCES photos(id),
  CHECK (
    (post_id IS NOT NULL AND photo_id IS NULL) OR
    (post_id IS NULL     AND photo_id IS NOT NULL)
  )
);

// Embedded (order + its items read together)
{ "_id": "order_123", "items": [{ "product_id": "p1", "qty": 2 }], "total": 59.98 }

// Referenced (customer loaded separately)
{ "_id": "order_123", "customer_id": "cust_456", "total": 59.98 }

// MongoDB indexes
db.users.createIndex({ email: 1 }, { unique: true });
db.orders.createIndex({ customer_id: 1, created_at: -1 });
db.articles.createIndex({ title: "text", body: "text" });
db.stores.createIndex({ location: "2dsphere" });

-- Step 1: Add nullable (deploy immediately — no lock on large tables)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- Step 2: Deploy app code that writes to new column
-- Step 3: Backfill
UPDATE users SET phone = '' WHERE phone IS NULL;
-- Step 4: Add constraint
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

-- Migration: 20250314120000_add_phone_to_users.sql

-- UP
BEGIN;
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
CREATE INDEX idx_users_phone ON users(phone);
COMMIT;

-- DOWN
BEGIN;
DROP INDEX idx_users_phone;
ALTER TABLE users DROP COLUMN phone;
COMMIT;

-- Analyze query plan
EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 123 AND status = 'pending';

# BAD: 1 query per order
for order in orders:
    customer = db.query("SELECT * FROM customers WHERE id = ?", order.customer_id)

# GOOD: single JOIN
results = db.query("""
    SELECT o.*, c.name AS customer_name
    FROM orders o
    JOIN customers c ON o.customer_id = c.id
    WHERE o.deleted_at IS NULL
""")

Extension Points

For advanced patterns not covered here, consider adding reference files:

• Database-specific: PostgreSQL vs MySQL vs SQLite variations
• Advanced patterns: Time-series, event sourcing, CQRS
• ORM integration: Prisma, TypeORM, SQLAlchemy
• Monitoring: Slow query logs, pg_stat_statements, query performance dashboards