Topolearn

Learn Database design

Model data, choose keys and indexes, and reason about transactions and scale

24 lessons · 5 hours · 4 levels · covers Database design Vendor-neutral (examples in PostgreSQL 16+)

Database design is the craft of turning a problem domain into tables, keys, constraints and indexes that stay correct and fast as data grows. It also covers how databases keep data safe under concurrency (transactions, isolation, MVCC) and how systems scale out with replication and sharding. These ideas apply to every relational database and shape when a NoSQL store is the better fit.

Sign in free to track progressOfficial docs ↗

Before you start

Database design builds on these; the tree links to the exact lessons you need.

SQLEssentials level

How deep do you want to go?

1. EssentialsRead and write simple code, do the everyday basics7 lessons · 1 hour in total
2. PracticalUse it productively at work, with the usual idioms15 lessons · 3 hours in total
3. AdvancedThe less common features, performance and trade-offs20 lessons · 4 hours in total
4. ExpertInternals and the edge cases experts know24 lessons · 5 hours in total

The Database design tree

Database designEntities, attributes and relationshipsEntities, attributesand relationshipsOne-to-many and many-to-manyOne-to-many andmany-to-manyNatural, surrogate and composite keysNatural, surrogateand composite keysConstraints encode business rulesConstraints encodebusiness rulesNormalisation: 1NF to BCNFNormalisation: 1NF toBCNFOLTP vs OLAP workloadsOLTP vs OLAPworkloadsTransactions and ACIDTransactions andACIDWhat an index is and what it costsWhat an index is andwhat it costsB-tree indexesB-tree indexesHash indexesHash indexesComposite and covering indexesComposite andcovering indexesHow the query planner chooses a planHow the queryplanner chooses a…Denormalisation on purposeDenormalisation onpurposeIsolation levels and anomaliesIsolation levels andanomaliesCommon modelling patternsCommon modellingpatternsCreating and altering tablesCreating and alteringtablesForeign keys and referential integrityForeign keys andreferential integrityPrimary keys and identity columnsPrimary keys andidentity columnsNOT NULL, UNIQUE, CHECK and DEFAULTNOT NULL, UNIQUE,CHECK and DEFAULTINSERT, UPDATE and DELETEINSERT, UPDATE andDELETEIndex basicsIndex basicsInner joinsInner joins
Essentials and Practical shown (15 of 24 lessons); the full list is below. Click a lesson to read it.

All 24 lessons, in order

Data modelling

  1. Entities, attributes and relationships · Essentials · 10 min

    Describe a domain as entities with attributes and the relationships between them before writing any SQL.

  2. One-to-many and many-to-many · Essentials · 10 min

    Represent 1:1, 1:N and M:N relationships with foreign keys and junction tables.

  3. Natural, surrogate and composite keys · Essentials · 10 min

    Choose primary keys: natural vs surrogate, composite keys, and why you still need unique constraints.

  4. Constraints encode business rules · Essentials · 8 min

    Use NOT NULL, CHECK, UNIQUE and foreign keys so the database itself rejects invalid data.

  5. Common modelling patterns · Practical · 12 min

    Soft deletes, history tables, polymorphic links, enums and EAV: when each pattern helps and what it costs.

Normalisation and schema shape

  1. Normalisation: 1NF to BCNF · Essentials · 14 min

    Remove redundancy with the normal forms so each fact is stored exactly once.

  2. OLTP vs OLAP workloads · Essentials · 8 min

    Recognise transactional and analytical workloads, because they lead to opposite design choices.

  3. Denormalisation on purpose · Practical · 10 min

    Duplicate or precompute data deliberately for read performance, and keep the copies correct.

  4. Zero-downtime schema changes · Expert · 14 min

    Evolve a live schema with expand-and-contract migrations, backfills in batches and lock-aware DDL.

Transactions and concurrency

  1. Transactions and ACID · Essentials · 12 min

    Group statements into all-or-nothing transactions and understand what atomicity, consistency, isolation and durability promise.

  2. Isolation levels and anomalies · Practical · 14 min

    Read uncommitted to serializable: which anomalies each level allows and what your database actually defaults to.

  3. Locking vs MVCC · Advanced · 14 min

    How databases implement isolation: lock-based concurrency control versus multi-version snapshots.

Indexes and query planning

  1. What an index is and what it costs · Practical · 10 min

    An index is a separate sorted structure that turns full scans into lookups, at a cost on every write.

  2. B-tree indexes · Practical · 12 min

    How B-trees keep keys sorted in shallow, balanced trees so equality, range and ORDER BY queries are fast.

  3. Hash indexes · Practical · 8 min

    Hash indexes map a hash of the key to rows: only equality lookups, but compact and constant-time.

  4. Composite and covering indexes · Practical · 12 min

    Order columns in multi-column indexes by how queries filter and sort, and cover queries to skip the table.

  5. How the query planner chooses a plan · Practical · 14 min

    Read execution plans: scans, join algorithms, row estimates and why the planner picks one plan over another.

  6. B-tree vs LSM-tree storage engines · Expert · 15 min

    Compare update-in-place B-trees with log-structured merge trees: read, write and space amplification.

Replication and scale-out

  1. Replication · Advanced · 14 min

    Copy data to other nodes for availability and read scaling: leader-follower, sync vs async, and replica lag.

  2. Partitioning and sharding · Advanced · 14 min

    Split big tables into partitions on one server, or shard data across servers, and choose a good partition key.

  3. Distributed transactions, sagas and outbox · Expert · 15 min

    Keep data consistent across services and shards: two-phase commit, sagas with compensation, and the transactional outbox.

Consistency and choosing a store

  1. CAP and PACELC · Advanced · 12 min

    During a network partition a distributed store must choose consistency or availability; otherwise it trades latency for consistency.

  2. Choosing SQL vs NoSQL · Advanced · 14 min

    Match the data model and access patterns to relational, document, key-value, wide-column or graph stores.

  3. Consistency models · Expert · 15 min

    Linearizability, sequential, causal and eventual consistency, plus session guarantees like read-your-writes.