Postgres Storytelling: Cunning Schema Design with Creative Data Modeling
summary
Designs a Postgres schema for a chess tournament: creates a chess schema, chess.players with serial primary key and case-insensitive unique name via CREATE UNIQUE INDEX (LOWER(name)); chess.games with foreign keys for white and black, a custom ENUM score = {white, black, draw} stored efficiently, and a uniqueness constraint across (white, black, phase, tournament) so players can only meet once per phase. Shows ALTER TABLE to add tournament and phase, demos inserting/violating constraints, and leaves preventing self-play as homework.
AI-written summary · about
chapters (10)
- 0:00Introduction
- 1:54Start of “story”
- 5:57Whiteboard explanation – unique IDs, DB constraints
- 8:17Demo: schema
- 11:40Demo: case insensitive, unique index
- 14:46Whiteboard explanation – ENUM type
- 16:27Demo: ENUM type, business logic
- 19:57Whiteboard explanation: declarative programming
- 22:10Demo: alter table
- 27:00End of story and conclusion
related talks
25mData modeling with PostgreSQL at the core of Software Development
48mAdvanced Postgres Schema Design (For Scalable Application Development) bySehrope Sarkuni
45mSELECT 'amazing_features' FROM "postgresql"
46mSQL for Nerds
47mLearning advanced SQL the weird (and hard) way
34mSimple and Awesome Database Tricks
30mLesser known features of PostgreSQL
24mPostgreSQL Design Patterns