Database schema design patterns are proven structural approaches to organizing tables, relationships, and data flows in relational and document databases. Effective schema design balances normalization for data integrity with denormalization for query performance, handles evolving requirements through migration-safe patterns, and scales from single-instance deployments to distributed multi-tenant architectures. While normalization eliminates redundancy, real-world systems often employ denormalization, partitioning, event sourcing, and flexible JSONB columns to meet specific read-heavy, historical, or schema-evolution needs. Understanding when to apply each pattern—and when to break traditional rules—separates maintainable production systems from brittle prototypes that fail under load or become impossible to evolve safely.
What This Cheat Sheet Covers
This topic spans 25 focused tables and 132 indexed concepts. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.
Table 1: Normalization Forms
The normal forms are a ladder of increasingly strict rules for splitting data so each fact lives in exactly one place. Most production schemas stop at 3NF or BCNF — the higher forms address rarer multivalued and join dependencies you'll only hit in genuinely complex many-to-many designs.
| Form | Example | Description |
|---|---|---|
CREATE TABLE orders ( order_id INT, product_id INT, qty INT) | • Eliminates repeating groups and ensures each column contains atomic values • All entries in a column must be of the same type • Each row must be uniquely identifiable | |
Split order_items with composite key (order_id, product_id) from orders table | • Removes partial dependencies on composite keys • Every non-key column must depend on the entire primary key, not just part of it • Only applies when you have a composite primary key | |
Move customer_city to separate customers table instead of orders | • Eliminates transitive dependencies where non-key columns depend on other non-key columns • Every non-key attribute must depend only on the primary key, not on another non-key attribute |