database-design-patterns

Normalization reduces data redundancy and maintains integrity; denormalization duplicates data to optimize read performance. Choose normalization for write-heavy, transactional systems (OLTP) and denormalization for read-heavy analytical workloads (OLAP) or when query speed outweighs storage costs.

Use NoSQL databases for unstructured data, horizontal scaling requirements, or when schema flexibility matters. Relational databases suit structured data with complex joins and ACID guarantees. Polyglot persistence uses both: SQL for transactional data, NoSQL for catalogs or time-series.

Sharding splits data across multiple servers by a key, distributing write load and storage. Replication copies data across servers for redundancy and read availability. Both address scalability but serve different purposes: sharding for capacity, replication for resilience.

Plan the target schema to match access patterns, map relational tables to document or key-value structures, handle denormalization and embedding, validate data integrity, and execute incremental migration with dual writes. Schema mismatch and cardinality changes are common migration challenges.

ACID databases prioritize consistency and partition tolerance but may reduce availability during conflicts; BASE systems favor availability and partition tolerance at eventual consistency. CAP theorem dictates you cannot guarantee all three; choose based on tolerance for stale reads and recovery time.

Yes. Caching reduces query load on the database and speeds repeated reads without schema changes. Layer caching in application code or use dedicated tools (Redis, Memcached). Cache invalidation and staleness are trade-offs; combine caching with selective denormalization for optimal scaling.