database-design

name	database-design
description	Database modeling and schema design best practices for relational and NoSQL databases. Use when designing new database schemas, creating migration files, choosing between database types, normalizing/denormalizing data, defining indexes, or reviewing existing database designs. Triggers on tasks involving table design, ER diagrams, schema migration, indexing strategy, query performance planning, or database architecture decisions.
metadata	{"version":"1.0.0"}

Database Design Best Practices

A comprehensive guide for designing efficient, maintainable, and scalable database schemas — covering relational modeling, NoSQL patterns, indexing strategies, migration management, and performance considerations.

When to Apply

• Designing a new database schema from scratch
• Creating or reviewing database migration files
• Choosing between relational (SQL) and NoSQL databases
• Normalizing or denormalizing existing schemas
• Defining indexes for query optimization
• Planning data partitioning or sharding strategies
• Designing audit trails, soft deletes, or multi-tenancy

---

1. Database Selection Guide

1.1 When to Use Relational (SQL)

• Strong data consistency requirements (ACID transactions)
• Complex relationships between entities (JOINs needed)
• Structured, predictable data models
• Regulatory compliance (financial, healthcare)
• Need for ad-hoc querying and reporting

Recommended: PostgreSQL (default choice), MySQL (web apps), SQLite (embedded/mobile)

1.2 When to Use NoSQL

• Flexible or evolving data schemas
• High write throughput with simple access patterns
• Large-scale horizontal scaling needed
• Document-oriented or graph data models
• Real-time analytics on large datasets

Recommended: MongoDB (documents), Redis (cache/sessions), DynamoDB (serverless), Neo4j (graphs)

1.3 Decision Matrix

Requirement	SQL	NoSQL (Document)	NoSQL (Key-Value)
Complex JOINs	Best	Avoid	N/A
Flexible schema	Limited	Best	Limited
ACID transactions	Full	Partial	Limited
Horizontal scaling	Hard	Easy	Easy
Ad-hoc queries	Strong	Moderate	Weak
Write-heavy workload	Moderate	Good	Best

---

2. Naming Conventions

2.1 General Rules

• Use snake_case for all database identifiers (tables, columns, indexes)
• Use singular table names: user, order, product (not users, orders)
• Use descriptive, unambiguous names: created_at (not date or ts)
• Prefix junction/join tables: order_item, user_role
• Avoid reserved words as names

2.2 Column Naming

Good:                    Bad:
created_at               date, ts, createdAt
updated_at               modified, upd_time
is_active                active, status_flag
user_id                  uid, userId, fk_user
total_amount             amount, total, price
deleted_at               is_deleted (use timestamp for soft delete)

2.3 Index Naming

Format: idx_{table}_{columns}

Examples:
idx_user_email              -- unique index on user.email
idx_order_user_created      -- composite on order(user_id, created_at)
idx_product_category_id     -- foreign key index

---

3. Schema Design Principles

3.1 Every Table MUST Have

Column	Type	Purpose
id	UUID or BIGSERIAL	Primary key (prefer UUID for distributed systems)
created_at	TIMESTAMPTZ	Record creation time
updated_at	TIMESTAMPTZ	Last modification time (auto-update)

3.2 Primary Key Strategy

Strategy	When to Use	Pros	Cons
UUID v7 (Recommended)	Distributed systems, public APIs	No ID enumeration, merge-safe	Larger storage, slower index
Auto-increment BIGINT	Single-database, internal use	Compact, fast, ordered	ID enumeration risk
ULID	Distributed, sortable needed	Sortable + unique	Less common tooling

3.3 Timestamps Best Practice

CREATE TABLE users (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email       VARCHAR(255) NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

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

CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

---

4. Data Types Guide

4.1 Choose the Right Type

Data	PostgreSQL Type	Avoid
Money	NUMERIC(19,4) or INTEGER (cents)	FLOAT, REAL
Email	VARCHAR(255)	TEXT (no length constraint)
URL	VARCHAR(2048)	TEXT
JSON data	JSONB	TEXT (storing serialized JSON)
Boolean	BOOLEAN	INTEGER (0/1)
IP address	INET	VARCHAR
Timestamp	TIMESTAMPTZ	TIMESTAMP (no timezone)
Enum values	VARCHAR + CHECK constraint	ENUM type (hard to alter)
UUID	UUID	VARCHAR(36)

4.2 Money Anti-Pattern

Incorrect:

price DECIMAL(10,2)   -- Precision loss with calculations
price FLOAT           -- Floating point rounding errors

Correct:

price_cents INTEGER   -- Store in smallest currency unit
-- or
price NUMERIC(19,4)   -- If decimal display needed

---

5. Normalization & Relationships

5.1 Normalization Levels

Level	Rule	Example
1NF	Atomic values, no repeating groups	Split phone_numbers into separate rows
2NF	No partial dependencies on composite keys	Move department_name from employee to department
3NF	No transitive dependencies	Move city_name from user to address table

5.2 When to Denormalize

Denormalize when:

• Read performance is critical and writes are infrequent
• Complex JOINs cause significant latency
• The data is read-heavy (reporting, analytics)
• You can tolerate eventual consistency

Common denormalization patterns:

• Counter cache: Store posts_count on user table
• Materialized path: Store category_path for tree traversal
• Embedded documents: Store frequently accessed related data

5.3 Junction Tables (Many-to-Many)

CREATE TABLE order_item (
    order_id   UUID NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id UUID NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity   INTEGER NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(19,4) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

Rules:

• Always use a composite primary key on the two foreign keys
• Add extra columns (quantity, metadata) directly to the junction table
• Choose ON DELETE CASCADE vs RESTRICT based on business logic

---

6. Indexing Strategy

6.1 Golden Rules of Indexing

1. Index every foreign key — Always create an index on FK columns
2. Index columns used in WHERE, JOIN, ORDER BY — These benefit most from indexes
3. Use composite indexes wisely — Order matters (leftmost prefix rule)
4. Don't over-index — Each index slows down writes (INSERT/UPDATE/DELETE)
5. Monitor and remove unused indexes — Check pg_stat_user_indexes

6.2 Composite Index Order

Put the most selective (highest cardinality) column first:

-- Good: status has low cardinality, created_at is high cardinality
CREATE INDEX idx_orders_status_created ON orders(status, created_at);

-- Good for query: WHERE status = 'active' ORDER BY created_at DESC
-- Also supports: WHERE status = 'active' (leftmost prefix)
-- Does NOT support: WHERE created_at > '2024-01-01' (skips leftmost column)

6.3 Partial Indexes

For queries that only target a subset of rows:

-- Only index active users (much smaller index)
CREATE INDEX idx_active_users_email ON users(email) WHERE is_active = true;

-- Only index recent orders
CREATE INDEX idx_recent_orders ON orders(created_at) WHERE created_at > NOW() - INTERVAL '30 days';

6.4 When NOT to Index

• Small tables (< 1000 rows) — full table scan is faster
• Columns with very low cardinality (e.g., boolean with 50/50 split)
• Tables with heavy write loads and rare reads
• Columns rarely used in WHERE/JOIN/ORDER BY

---

7. Soft Delete & Audit Trail

7.1 Soft Delete Pattern

CREATE TABLE products (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        VARCHAR(255) NOT NULL,
    deleted_at  TIMESTAMPTZ,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Always filter out soft-deleted records
CREATE VIEW active_products AS
SELECT * FROM products WHERE deleted_at IS NULL;

7.2 Audit Trail Pattern

CREATE TABLE audit_logs (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    table_name  VARCHAR(64) NOT NULL,
    record_id   UUID NOT NULL,
    action      VARCHAR(10) NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
    old_data    JSONB,
    new_data    JSONB,
    changed_by  UUID NOT NULL,
    changed_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

---

8. Migration Best Practices

8.1 Migration Rules

1. Always make migrations reversible — Provide both up and down methods
2. Never modify existing migrations — Create new ones instead
3. Test migrations on a copy of production data first
4. Use transactions for schema changes
5. Add indexes concurrently in production (PostgreSQL: CREATE INDEX CONCURRENTLY)
6. Break large migrations into smaller, independent steps

8.2 Safe Migration Patterns

-- Safe: Add new column with default (PostgreSQL 11+)
ALTER TABLE users ADD COLUMN avatar_url VARCHAR(512);

-- Safe: Add nullable column, backfill, then add NOT NULL constraint
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
-- ... backfill data ...
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

-- Safe: Rename column (3-step for zero-downtime)
-- Step 1: Add new column
ALTER TABLE users ADD COLUMN full_name VARCHAR(255);
-- Step 2: Backfill from old column
UPDATE users SET full_name = first_name || ' ' || last_name;
-- Step 3: Drop old column (after app code updated)
ALTER TABLE users DROP COLUMN first_name, DROP COLUMN last_name;

---

9. Multi-Tenancy Design

9.1 Strategies

Strategy	Isolation	Complexity	When to Use
Shared DB, shared schema	Lowest	Lowest	Simple SaaS, trust between tenants
Shared DB, separate schemas	Medium	Medium	Moderate isolation needed
Separate databases	Highest	Highest	Enterprise, strict compliance

9.2 Tenant Column Pattern (Shared Schema)

CREATE TABLE documents (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id),
    title       VARCHAR(255) NOT NULL,
    content     TEXT,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Always include tenant_id in queries and indexes
CREATE INDEX idx_documents_tenant ON documents(tenant_id, created_at DESC);

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

---

10. Schema Review Checklist

• Every table has id, created_at, updated_at
• All foreign keys have indexes
• All columns have appropriate data types (no TEXT for structured data)
• Money values use NUMERIC or integer cents
• All timestamps use TIMESTAMPTZ (with timezone)
• Sensitive data (passwords, tokens) is never stored in plain text
• All VARCHAR columns have reasonable length limits
• Tables have appropriate constraints (NOT NULL, CHECK, UNIQUE)
• ON DELETE behavior is explicitly defined for all foreign keys
• Naming follows snake_case convention consistently
• Junction tables have composite primary keys
• Large tables have partitioning strategy if needed

---

11. Quick Reference: Common Anti-Patterns

Anti-Pattern	Problem	Solution
No foreign key constraints	Data integrity violations	Always define FK constraints
Storing JSON as TEXT	No query capability	Use JSONB type
FLOAT for money	Rounding errors	Use NUMERIC or integer cents
Missing indexes on FKs	Slow JOINs	Index every foreign key
ENUM types	Hard to modify	Use VARCHAR + CHECK
TIMESTAMP without timezone	Timezone bugs	Always use TIMESTAMPTZ
Over-indexing	Slow writes	Monitor and remove unused indexes
Monolithic tables	Poor performance	Partition or split tables
No soft delete	Permanent data loss	Add deleted_at column
God table (100+ columns)	Hard to maintain	Normalize into related tables