Data modeling with PostgreSQL at the core of Software Development
summary
Builds a working chess-tournament schema in PostgreSQL that enforces rules in the database so less app code is needed: a chess.players table with surrogate integer IDs, a function-based unique index using lower(name) to prevent case collisions, a chess.games table with foreign keys to players and tournaments and a uniqueness constraint preventing duplicate white/black in the same tournament phase, and a custom enum score type for results. All SQL is tested on Postgres 18 and the schema was used in a real tournament last year.
AI-written summary · about
chapters (9)
- 0:00Introduction and welcome
- 1:48Chess tournament use case
- 4:13Designing the players table
- 9:51Data integrity with constraints
- 12:19Modeling game results with enums
- 16:30Applying data modeling principles
- 18:58Adding tournament phases
- 21:44Evolving the design
- 23:07Closing and key takeaways
related talks
28mPostgres Storytelling: Cunning Schema Design with Creative Data Modeling
48mAdvanced Postgres Schema Design (For Scalable Application Development) bySehrope Sarkuni
46mSQL for Nerds
47mLearning advanced SQL the weird (and hard) way
46mUse PostgreSQL to Step Up Your Game
45mGraph databases, PostgreSQL and SQL/PGQ
29mPostgres Demystified
40mLet's SQL Like It's 1992!