Lesson 18 · Python
Databases: Schema Design
Design the tables themselves: natural versus surrogate keys, foreign keys and referential integrity, junction tables for many-to-many, the anomalies that punish redundancy, constraints, indexes and migrations.
Prerequisites: Databases: Joins, Aggregates & Subqueries
Learning objectives
By the end of this lesson, you will be able to:
- Choose between natural and surrogate primary keys and defend the choice
- Connect tables with foreign keys and explain what referential integrity enforces - and how to switch it on in SQLite
- Model one-to-many and many-to-many relationships, with junction tables that carry the relationship's own facts
- Diagnose update, insert and delete anomalies in a redundant table and fix them by splitting it
- Write constraints that reject bad data at write time, reason about index costs, and evolve a schema with ordered migrations