Database Design Patterns for Modern Web Applications

Database design affects performance, scalability, and maintainability. Relational vs document, schema design, indexing — these choices compound. Here are the patterns we use for modern web applications.

Table of Contents

• Relational (PostgreSQL, MySQL)
• Document (MongoDB)
• Schema Design Principles
• Indexing Strategy
• Scaling Patterns
• Frequently Asked Questions

Relational (PostgreSQL, MySQL)

Best for: structured data, relationships, transactions, reporting. Tables, rows, foreign keys. ACID compliance. Use when: users, orders, invoices, multi-entity relationships. PostgreSQL is our default for business apps.

Document (MongoDB)

Best for: flexible schemas, nested data, rapid iteration. JSON-like documents. Use when: content, catalogs, event logs, schema varies by document. Less ideal for complex joins and transactions.

Schema Design Principles

• Normalize for integrity — avoid duplication, use foreign keys
• Denormalize for read performance — when reads >> writes, cache or duplicate
• Use UUIDs for public IDs — avoid sequential ID leakage
• Soft delete — add deleted_at instead of hard delete for audit
• Timestamps — created_at, updated_at on every table

Indexing Strategy

Index columns used in WHERE, JOIN, ORDER BY. Don't over-index — each index slows writes. Composite indexes for multi-column queries. Use EXPLAIN to analyze query plans.

Scaling Patterns

• Read replicas — offload reads to replicas, write to primary
• Connection pooling — PgBouncer, RDS Proxy
• Caching — Redis for hot data
• Sharding — split by tenant_id or range (advanced)

Frequently Asked Questions