SQL and Relational Database Design
A complete course in relational databases: modelling, joins, transactions and isolation, index design, reading EXPLAIN, migrations and operating a database.
- Modules
- 03
- Lessons
- 07
- Reading time
- 4H
- Words
- 14,053
What you will be able to do
- Model a domain as tables with keys and constraints that make bad data impossible.
- Write joins, aggregations and window functions that answer real questions.
- Use transactions and isolation levels with a clear idea of what each protects against.
- Design an index for a query shape rather than adding indexes and hoping.
- Read an EXPLAIN plan and tell a scan from a seek.
- Change a schema on a live database without an outage.
Before you start
Comfort with a command line and the ability to install a database server or run one in a container. No prior SQL assumed.
Still being written
3 of 6 modules are finished and readable now. The remaining 3 are in progress and will appear here as they are published. Nothing already written is removed, and the lessons below will not change underneath you.
Almost every application bug that survives to production is a data bug. A missing constraint lets a duplicate row through, an index that did not match the query shape turns a millisecond lookup into a second-long scan, and a transaction boundary drawn in the wrong place produces a state that nothing in the code can explain.
This course treats the database as the part of the system where correctness is actually enforceable. It starts with modelling, because constraints written into the schema are the only validation that cannot be bypassed by a new code path. It moves through querying, transactions and isolation, then spends two modules on the parts people learn by accident: index design and schema change on a live system.
Every example runs on a real database with a real dataset, and every performance claim is demonstrated with an execution plan rather than asserted. The SQL is portable across PostgreSQL, MySQL and MariaDB except where a specific engine behaviour is the point, in which case it is named.
Curriculum
3 modules, 7 lessons
Thinking in tables
Relations, keys, constraints and normalisation, and treating the schema as a contract rather than a dumping ground.
3 lessons · 1H 35M
Querying
SELECT, joins, aggregation, subqueries and window functions, with the mental model behind each.
3 lessons · 1H 45M
Writing data safely
INSERT, UPDATE and DELETE, transactions, isolation levels, locking and deadlocks.
1 lessons · 40M
Go deeper