What do you think about when designing a database that needs to grow?

Designing a scalable database schema requires balancing normalized data integrity with query performance, while planning for growth.

Start with normalization to eliminate redundancy and maintain data integrity. Third Normal Form (3NF) is typically the target for OLTP systems. This prevents update anomalies and reduces storage, but may require joins that impact query performance.

Strategic denormalization trades some redundancy for query speed. When you find yourself frequently joining the same tables, consider storing computed or duplicated data. Document the trade-offs and ensure you have processes to maintain consistency.

Indexing is crucial for query performance. Index columns used in WHERE clauses, JOIN conditions, and ORDER BY. Composite indexes should follow the order of columns in queries. But indexes slow down writes and consume storage—don't over-index.

Choose appropriate data types: use the smallest type that fits your data, consider UUIDs vs auto-increment for primary keys (UUIDs are better for distributed systems), and understand when to use specialized types like JSON columns.

Plan for partitioning as data grows: horizontal partitioning (sharding) distributes rows across tables/databases based on a key; vertical partitioning splits tables by columns. Choose partition keys carefully based on access patterns.

Consider your scaling strategy early: read replicas for read-heavy workloads, connection pooling, caching layers (Redis), and whether you might need to shard. Some decisions are hard to change later, so understanding growth projections helps inform initial design choices.