Database Design Patterns for Web Applications

Building Data Foundations That Scale

Good database design is invisible—applications built on solid foundations perform well and evolve gracefully. Poor design becomes increasingly painful as applications grow. Let's explore patterns that set you up for success.

Relational vs. NoSQL

When Relational Databases Excel

• Complex relationships between entities
• Need for ACID transactions
• Structured, consistent data
• Complex querying requirements
• Data integrity is paramount

When NoSQL Makes Sense

• Highly variable document structures
• Massive scale requirements
• High-velocity writes
• Simple access patterns
• Geographic distribution needs

The Reality

Most applications benefit from relational databases. NoSQL solves specific problems—don't choose it because it's trendy.

Normalization Fundamentals

Purpose of Normalization

• Eliminate data redundancy
• Ensure data integrity
• Reduce update anomalies
• Create a logical, maintainable structure

Key Normal Forms

1NF: Single values per cell, unique rows

2NF: All non-key columns depend on entire primary key

3NF: No transitive dependencies between non-key columns

Practical Approach

Aim for 3NF in most cases. Denormalize intentionally for performance, not accidentally from poor design.

Strategic Denormalization

When to Denormalize

• Read-heavy workloads with stable data
• Expensive joins impacting performance
• Caching frequently accessed aggregations
• Report-specific tables

Controlled Denormalization

• Document the trade-offs
• Maintain source of truth in normalized form
• Update denormalized data consistently
• Consider materialized views

Indexing Strategy

Index Selection

• Primary keys are automatically indexed
• Index foreign keys for join performance
• Index columns used in WHERE clauses
• Index columns used for ORDER BY

Composite Indexes

Order matters:

• Most selective columns first
• Columns used for range queries last
• Match query patterns

Index Pitfalls

• Over-indexing slows writes
• Unused indexes waste storage
• Missing indexes cause full table scans
• Monitor and adjust based on actual queries

Schema Evolution

Migration Best Practices

• Version control all schema changes
• Make changes additive when possible
• Plan for rollback
• Test migrations with production-scale data

Zero-Downtime Migrations

For production applications:

1. Add new column (nullable)
2. Deploy code that writes to both
3. Backfill existing data
4. Deploy code that reads from new column
5. Remove old column

Handling Relationships

One-to-Many

Classic foreign key relationship:

• Parent table referenced by child
• Cascade delete or set null on delete
• Index foreign key column

Many-to-Many

Junction tables connect entities:

• Composite primary key or surrogate key
• Additional columns for relationship metadata
• Index both foreign keys

Self-Referential

Same table relates to itself:

• Employee/manager relationships
• Category hierarchies
• Comment threading

Common Patterns

Soft Deletes

Keep data but mark as deleted:

• deleted_at timestamp column
• Filter queries by default
• Allows recovery and audit trail
• Consider eventual hard delete

Audit Trails

Track who changed what when:

• created_at, updated_at timestamps
• created_by, updated_by user references
• Consider separate audit tables for history

Multi-Tenancy

Multiple customers in single database:

• Tenant ID on all tables
• Row-level security enforcement
• Index tenant ID for performance
• Consider separate schemas for isolation

Query Optimization

Explain Your Queries

Use EXPLAIN to understand:

• Which indexes are used
• Join strategies
• Row estimates
• Cost calculations

Common Optimizations

• Add appropriate indexes
• Rewrite subqueries as joins
• Limit result sets
• Avoid SELECT *
• Use appropriate data types

Data Types

Choose Appropriate Types

• VARCHAR for variable text, not unlimited TEXT
• Specific numeric types for numbers
• UUID vs. integer for primary keys
• TIMESTAMP WITH TIME ZONE for times

UUID Considerations

Pros: Globally unique, no coordination needed, predictable size Cons: Larger than integers, randomness impacts index performance

Transaction Design

ACID Properties

• Atomicity: All or nothing
• Consistency: Valid state to valid state
• Isolation: Concurrent transactions don't interfere
• Durability: Committed data persists

Isolation Levels

Know the tradeoffs:

• Read Uncommitted: Fast but dirty reads possible
• Read Committed: Most common default
• Repeatable Read: No phantom reads
• Serializable: Strongest isolation, slowest

Planning for Scale

Horizontal Considerations

• Design with sharding in mind
• Avoid cross-shard transactions
• Choose shard keys carefully
• Consider read replicas for read-heavy loads

Vertical Considerations

• Connection pooling
• Query result caching
• Appropriate hardware sizing
• Index maintenance

Good database design requires understanding your data, access patterns, and growth expectations. Take time to design properly—it's much easier than fixing a poor design under production load.