Organize requirements, build your schema-design skills.
Turn requirements into entities, relationships, keys, and a relational schema — one decision at a time.
You can aim for: Database design & operationsER models and data modeling
Entities and Attributes
Deciding what gets its own identity and what is stored as a fact about it.
Relationships and Cardinality
Showing how entities connect and how many instances may participate.
Required and Optional Participation
Distinguishing how many may participate from whether participation is required.
Resolving a Many-to-Many Relationship
Introducing an associative entity to record each pairing and its attributes.
Choosing Keys
Comparing natural, surrogate, and composite identifiers.
From an ER Model to a Relational Schema
Turning entity boxes, attributes, and relationships into tables, columns, and foreign keys.
Constraints and referential integrity
Primary Keys and UNIQUE
Protecting the primary identifier and additional candidate keys.
NOT NULL and CHECK
Requiring a value and limiting which values are accepted.
Foreign Keys
Ensuring that every stored reference points to an existing row.
ON DELETE Referential Actions
Choosing what happens to child rows when a referenced row is deleted.
Invariants Protected by Constraints
Matching each data rule to the database constraint that enforces it.
Normalization
Update Anomalies and Normalization
Seeing how repeated facts create contradictory rows.
Functional Dependencies and Candidate Keys
Drawing which attributes determine other attributes.
From 1NF to 2NF and 3NF
Separating repeating groups, partial dependencies, and transitive dependencies.
When to Denormalize
Trading extra write complexity for a measured read benefit.
Indexes and query plans
Sequential Scan vs Index Scan
Comparing every-page scanning with targeted index lookup.
Inside a B-tree Index
Following equality, range, and ordered traversal through a balanced tree.
Multicolumn Index Column Order
Seeing how the leading columns shape searchable ranges.
Index Only Scans and Covering Indexes
Returning query data from index entries while accounting for visibility.
When an Index Does Not Help
Comparing selectivity, expressions, and write overhead.
Reading EXPLAIN and EXPLAIN ANALYZE
Separating estimates from measurements in a plan tree.
The N+1 Query Problem
Counting application round trips for parent and child rows.
Transactions and concurrency
Transactions and Atomicity
Committing or rolling back several statements as one unit.
Isolation Levels and Read Anomalies
Comparing snapshots and anomalies under PostgreSQL isolation levels.
Lost Updates and Row Locks
Preventing two stale calculations from overwriting each other.
The Intuition Behind MVCC
Showing row versions and the snapshots that can see them.
Deadlocks
Finding and breaking a cycle of lock waits.
Optimistic vs Pessimistic Locking
Comparing version checks with locks acquired before editing.