How would you design a scalable relational database schema?

Long Answer

Designing a relational database schema for a high-transaction web application is a balancing act between normalization for correctness, query performance for latency goals, and scalability for sustained throughput. The architecture should start from the domain, encode business invariants in the model, and then selectively introduce performance-oriented patterns with clear guardrails.

1) Domain-first modeling, then principled normalization
Begin with a ubiquitous language and entity boundaries (customers, orders, payments, inventory). Normalize core entities to 3NF or BCNF so functional dependencies are explicit, update anomalies are eliminated, and the optimizer can reason over small, well-defined rows. Use surrogate primary keys (BIGINT/UUIDv7) for stability, while preserving natural keys with UNIQUE constraints. Enforce integrity in the database: foreign keys, CHECK constraints, and enumerations as reference tables, not ad-hoc strings.

2) Indexing strategy aligned to access paths
Indexes exist to serve queries, not ideology. From your most frequent and most expensive queries, define composite indexes with left-to-right selectivity, cover projections (INCLUDE columns), and prefer narrow datatypes to reduce B-tree height. Use partial/filtered indexes for skew (e.g., “active=true”). Maintain one clustered/primary index that supports write locality; avoid overlap and duplicate shapes. Revisit plans under realistic cardinalities and parameter sniffing scenarios; add hints sparingly and remove when data distribution changes.

3) Write-optimized OLTP and transaction discipline
Keep transactions short and predictable: issue reads first, writes second, commit quickly. Choose isolation that matches risk: READ COMMITTED with retry logic is a pragmatic default; escalate to REPEATABLE READ or SERIALIZABLE only around critical invariants (double-spend, inventory). Prefer optimistic locking (rowversion, xmin) for high contention entities; fall back to pessimistic locks only for scarce resources. Batch inserts/updates with array parameters or COPY-like bulk paths to minimize round trips.

4) Targeted denormalization and read models
Normalization guards correctness; denormalization serves latency. For hot paths (order summaries, balances), create materialized views or precomputed aggregates updated by triggers, CDC pipelines, or scheduled jobs. Consider a CQRS-style projection (read model) stored in the same RDBMS for simplicity. Keep the write model canonical; make denormalized artifacts disposable and rebuildable. For lookups across microservices, replicate small reference tables, not big mutable facts.

5) Horizontal scale with partitioning and replicas
For sustained scalability, partition large tables by business key (tenant_id) or time (range) to bound indexes and speed pruning. Co-partition child tables to preserve locality. Introduce read replicas to offload reporting and fan-out reads; direct strictly read-only workloads there and tolerate replica lag with monotonic reads where needed. If multi-region latency matters, prefer regional read replicas with request affinity while keeping a single write leader; move to multi-leader only with rigorous conflict rules.

6) Physical design and data hygiene
Choose compact types (INT vs BIGINT where possible, NUMERIC with sane precision). Store timestamps in UTC, normalize time zones in the app. Avoid large unbounded TEXT in OLTP tables; push blobs to object storage and reference by key. Compress historical partitions. Keep audit/history as append-only tables, not triggers on every table unless required; use CDC for downstream consumers. Archive cold partitions to cheaper storage with a clear retention policy.

7) Schema evolution and safety
Evolve the database schema with additive, backward compatible migrations: add columns nullable with defaults, backfill in batches, then enforce constraints. Split “expand → migrate → contract” into separate deploys and hide behind feature flags. Use online DDL tools and lock-aware windows. Maintain automated rollback scripts and strict semantic versioning of DDL. Validate changes with shadow reads or dual-writes during risky migrations.

8) Observability, SLOs, and guardrails
Define SLOs: P95 latency for key queries, replication lag thresholds, deadlock rate budgets. Monitor plan regressions, bloated indexes, and vacuum/autovacuum health (or equivalent). Capture slow query logs, sample plans, and track cardinalities over time. Establish ownership per table and index with a “drop budget” and a documented reason-to-exist. Regularly run index-usage reports and prune.

9) Microservices and boundaries
When using microservices, avoid accidental distributed joins. Keep each service as the authority of its write model; others read via APIs, CDC, or event streams. If one database is shared, enforce schemas per service and read-only roles across boundaries. Favor asynchronous integration; avoid two-phase commits unless absolutely necessary.

This approach yields a relational database schema that preserves integrity through normalization, achieves query performance with purpose-built indexes and read models, and delivers scalability with partitioning, replicas, and disciplined evolution—battle-ready for high-TPS workloads.

‍

Table

‍

Common Mistakes

• Over-denormalizing early, duplicating truths and creating update anomalies.
  
• Indexing everything “just in case,” inflating write latency and memory, with near-duplicate composites.
  
• Long, chatty transactions that mix user think time with locks, inviting deadlocks and timeouts.
  
• Ignoring skew: a single hot partition or tenant ruins P99s; no partial indexes or routing.
  
• Pushing blobs into OLTP rows, bloating pages and evicting hot keys from memory.
  
• One-shot “big bang” migrations that lock tables; no expand→contract plan.
  
• Using SERIALIZABLE everywhere or, conversely, ignoring isolation entirely.
  
• Relying on synthetic benchmarks; not validating under production-like concurrency and data volume.
  
  

Sample Answers (Junior / Mid / Senior)

Junior:
“I would normalize to 3NF, add foreign keys and indexes for common queries, and keep transactions short. For performance I would add composite indexes based on filters and order by. If reads are slow, I would create a materialized view for summaries and consider a read replica.”

Mid:
“My database schema design starts domain-first, normalized with strict constraints. I derive indexes from the top N queries and use covering composites. I keep writes lean with optimistic concurrency and batch I/O. For scalability, I partition large tables by tenant or time and use read replicas for reports. Migrations follow expand→migrate→contract with online DDL.”

Senior:
“I balance normalization and query performance by keeping the write model canonical and projecting denormalized read models via CDC. Indexes are selective, minimal, and monitored for usage. I partition for bounded scans and co-partition children. Transactions are short with retries, isolation elevated only where invariants demand. Evolution is feature-flagged with shadow reads, and observability tracks plans, lag, and P95/P99.”

‍

Evaluation Criteria

Look for a coherent relational database schema strategy that starts normalized for correctness, then introduces targeted denormalization for hot reads. Strong answers tie indexes to access paths (composite/covering/partial) and treat transactions as short, predictable units with appropriate isolation and optimistic locking. Scalability appears via partitioning, replicas, and routing; evolution is safe through expand→migrate→contract and online DDL. Observability is first-class: slow logs, plan tracking, SLOs. Red flags: blanket denormalization, “index everything,” long transactions, blob-stuffed OLTP tables, and breaking migrations that assume downtime. The best responses reference real-world trade-offs and capacity planning.

‍

Preparation Tips

• Model a small domain and normalize to 3NF; add FKs and CHECK constraints.
  
• Capture the top 10 queries; design composite and covering indexes for each, test with realistic data.
  
• Build a materialized view for a dashboard and script refresh/invalidations.
  
• Implement optimistic concurrency (rowversion/xmin) and demonstrate retry logic.
  
• Partition a large table by time and benchmark pruned scans; add a read replica and route reporting traffic.
  
• Practice expand→migrate→contract with online DDL on a non-trivial table, including backfill batches.
  
• Enable slow query logs, collect plans, and track P95/P99 latency before/after each change.
  
  

Real-world Context

An e-commerce platform normalized orders and payments, then added a materialized “order_summary” table fed by CDC; checkout write latency stayed sub-30 ms while the dashboard delivered sub-100 ms reads at peak. A fintech partitioned transactions by month and tenant, pruning scans and keeping indexes tiny; nightly jobs fell from hours to minutes. A SaaS vendor trimmed 40% of indexes after usage analysis, freeing memory and improving write throughput by double digits. Another team moved images to object storage and reduced page bloat, lifting buffer cache hit rates and stabilizing P99s. In each case, correctness came from normalization, speed from purpose-built indexes and projections, and scalability from partitioning and replicas.

‍

Key Takeaways

• Normalize first for integrity; denormalize surgically for hot reads.
  
• Design indexes from real access paths; keep them minimal and covering.
  
• Keep OLTP transactions short; use optimistic concurrency and appropriate isolation.
  
• Scale with partitioning, read replicas, and compact physical design
  
• Migrate with expand→migrate→contract and monitor plans, lag, and tail latency.
  
  

Practice Exercise

Scenario:
You are building the relational database schema for a multi-tenant high-transaction web application that handles orders, payments, and ledger entries. P95 read target is <100 ms; write target is <30 ms at 2k TPS sustained, spiking to 8k TPS.

Tasks:

1. Model tenants, customers, orders, order_items, payments, and a ledger table. Normalize to 3NF with FKs and CHECK constraints for states and amounts.
   
2. Propose primary keys and three composite indexes derived from: (a) recent orders per tenant, (b) payment lookups by external_id, (c) ledger queries by tenant + date. Include included columns.
   
3. Define transaction patterns for checkout: reads first, writes second, commit fast; specify isolation and retry policy.
   
4. Add one materialized view or projection that powers an orders dashboard; describe its refresh policy and invalidation triggers.
   
5. Partition at least one big table (orders or ledger) by month and tenant; explain pruning and co-partitioning.
   
6. Outline an expand→migrate→contract plan to add “refund_reason” to payments without downtime.
   
7. Provide observability: SLOs, slow log thresholds, and plan sampling cadence.
   
   

Deliverable:
A concise architecture and DDL+index script demonstrating balanced normalization, query performance, and scalability, plus a short load-test plan showing targets are met.