Database Design Cheat Sheet

Updated 2026-04-29

Next Topic: Database Migration and DevOps Cheat Sheet

Database design is the systematic process of structuring data within a relational database to minimize redundancy, ensure integrity, and optimize query performance—foundational to virtually every application that persists data. Whether building an e-commerce platform or a clinical trial management system, effective database design determines whether your application scales gracefully or collapses under load. At its core, database design balances normalization (eliminating redundancy through structured decomposition) with denormalization (strategic redundancy for performance), guided by principles like ACID guarantees, proper indexing, and constraint enforcement. The most critical concept to internalize: your schema is a contract—once data accumulates and systems depend on it, structural changes become exponentially costlier, making upfront design decisions disproportionately impactful.

What This Cheat Sheet Covers

This topic spans 16 focused tables and 140 indexed concepts. Below is a complete table-by-table outline of this topic, spanning foundational concepts through advanced details.

Table 1: Normalization FormsTable 2: Key TypesTable 3: Relationship Types and CardinalityTable 4: ConstraintsTable 5: Index TypesTable 6: Data Types SelectionTable 7: Schema Design PatternsTable 8: Denormalization TechniquesTable 9: Partitioning StrategiesTable 10: Transactions, Locking & IsolationTable 11: ER Modeling ConceptsTable 12: Functional DependenciesTable 13: Naming ConventionsTable 14: Query Performance OptimizationTable 15: Common Design Anti-PatternsTable 16: Advanced Concepts

Table 1: Normalization Forms

Form	Example	Description
First Normal Form (1NF)	`Orders(OrderID, Items: "A,B,C")` → `OrderItems(OrderID, Item)`	• Eliminates repeating groups and ensures atomic values • each cell contains a single value, not arrays or comma-separated lists.
Second Normal Form (2NF)	`OrderItems(OrderID, ProductID, ProductName, Qty)` → split ProductName to Products table	• Removes partial dependencies • all non-key attributes must depend on the entire primary key, not just part of a composite key.
Third Normal Form (3NF)	`Employees(EmpID, DeptID, DeptLocation)` → DeptLocation moves to Departments table	• Eliminates transitive dependencies • non-key attributes cannot depend on other non-key attributes, only on the primary key.
Boyce-Codd Normal Form (BCNF)	`CourseOffering(CourseID, Instructor, Room)` with Instructor → Room violates BCNF	• Stronger than 3NF • every determinant must be a candidate key • resolves anomalies where multiple candidate keys overlap.

Table 1: Normalization Forms

Form	Example	Description
First Normal Form (1NF)	`Orders(OrderID, Items: "A,B,C")` → `OrderItems(OrderID, Item)`	• Eliminates repeating groups and ensures atomic values • each cell contains a single value, not arrays or comma-separated lists.
Second Normal Form (2NF)	`OrderItems(OrderID, ProductID, ProductName, Qty)` → split ProductName to Products table	• Removes partial dependencies • all non-key attributes must depend on the entire primary key, not just part of a composite key.
Third Normal Form (3NF)	`Employees(EmpID, DeptID, DeptLocation)` → DeptLocation moves to Departments table	• Eliminates transitive dependencies • non-key attributes cannot depend on other non-key attributes, only on the primary key.
Boyce-Codd Normal Form (BCNF)	`CourseOffering(CourseID, Instructor, Room)` with Instructor → Room violates BCNF	• Stronger than 3NF • every determinant must be a candidate key • resolves anomalies where multiple candidate keys overlap.