Database Design for Scalable Applications

Good database design is invisible when done right and expensive to fix when done wrong — schema migrations on a live system with real data are far harder than getting the model right early.

1. Normalize First, Denormalize Deliberately

Start with a normalized schema (3NF) to avoid update anomalies and data duplication. Denormalize only for measured performance reasons — a read-heavy reporting table, a cache column for a frequently joined value — and document why.

2. Choosing SQL vs NoSQL

• PostgreSQL/MySQL — default choice for relational data with clear entities and transactions
• MongoDB — useful for genuinely document-shaped data with flexible, evolving schemas
• Redis — caching, session storage, and rate limiting, not primary data storage

Most applications — including most "we need NoSQL for scale" cases — are correctly served by PostgreSQL with proper indexing. Reach for NoSQL when the data model genuinely doesn't fit relational structure, not by default.

3. Indexing Strategy

• Index columns used in WHERE, JOIN, and ORDER BY clauses
• Composite indexes should match query patterns, ordered by selectivity
• Avoid over-indexing — every index slows down writes
• Use EXPLAIN ANALYZE to verify the planner is actually using your indexes

An index you never measured is a guess. Profile real query patterns before adding indexes, not after a slow query report from production.

4. Scaling Reads and Writes

• Read replicas for read-heavy workloads — route reporting and analytics queries away from primary
• Connection pooling (PgBouncer) to avoid exhausting database connections under load
• Partitioning large tables by date or tenant once single-table performance degrades
• Caching layer (Redis) in front of expensive, frequently repeated queries

5. Multi-Tenant and SaaS Schema Patterns

For SaaS applications, the schema decision that matters most is tenancy isolation: shared tables with a tenant_id column and composite indexes on (tenant_id, ...) are the right default for most products — see the SaaS architecture guide for the full trade-off breakdown.

6. Migrations as a First-Class Practice

• Version-controlled migrations (Flyway, Prisma Migrate, Liquibase)
• Backward-compatible migrations for zero-downtime deploys — add columns nullable first, backfill, then constrain
• Never run destructive migrations without a tested rollback path

Bringing It Together

The database is the part of your system that's hardest to change after launch. Investing in schema design, indexing discipline, and a clear scaling path early pays off far more than optimizing application code later.

I'm Sandaruwan Jayasundara — Senior Software Engineer | Full Stack Developer. See how to build a SaaS application or get in touch about your data architecture.