New
Pages
328
Published
2010
SQL
✨ New
SQL Antipatterns
Avoiding the Pitfalls of Database Programming
Recognize and fix the SQL mistakes that silently wreck your schema, queries, and application code.
SQL Antipatterns by Bill Karwin catalogs 24 common mistakes developers and DBAs make when designing schemas, writing queries, and working with application code. Each chapter names the antipattern, explains why it seems like a good idea at first, shows the damage it causes, and gives you a concrete alternative. If you write SQL beyond simple SELECT statements, this book will change how you think about your database.
About this book
Most SQL mistakes are not random. Developers reach for the same flawed solutions again and again: storing comma-separated lists in a single column, building entity-attribute-value tables to avoid adding columns, writing queries that silently return wrong results. These patterns have names, root causes, and better alternatives.
SQL Antipatterns names 24 of the most common mistakes across four categories: logical database design, physical database design, query writing, and application development. For each one, Bill Karwin walks you through why the antipattern happens, what goes wrong in practice, and how to solve the underlying problem the right way.
The format is deliberately diagnostic. Each chapter opens with a realistic scenario — a feature request, a performance complaint, a data integrity bug — and shows you exactly where the reasoning goes wrong. By the time you reach the solution, you understand it, not just the rule that forbids the antipattern.
Topics covered include:
- Jaywalking: storing multiple values in a single column and the joins you cannot write as a result
- Naive Trees: adjacency lists that cannot represent hierarchy without recursive queries
- Entity-Attribute-Value: the schema-less table that defeats indexing, constraints, and reporting
- Polymorphic Associations: foreign keys that point at multiple tables and the integrity you lose
- INDEX SHOTGUN: adding indexes everywhere until writes slow to a crawl
- Implicit Columns: SELECT * and INSERT without a column list, and why they break silently
- Magic Beans: letting your Active Record class become the entire application layer
The book targets relational databases generally and calls out MySQL, PostgreSQL, Oracle, and SQL Server behavior where the platforms differ. You do not need a specific database to benefit — the concepts apply wherever you write SQL.
If you are past the tutorial stage and starting to own production schemas, this is the book that closes the gap between code that works and code that holds up. Bill Karwin has spent decades in the MySQL ecosystem and on Stack Overflow answering the exact questions this book addresses. The advice here is precise, battle-tested, and immediately usable.
🎯 What you'll learn
- Identify 24 named antipatterns by their symptoms before they cause production incidents
- Design schemas that enforce data integrity at the database level, not just in application code
- Store hierarchical data using closure tables or nested sets instead of broken adjacency lists
- Write queries that return correct results even when NULL values, aggregates, or outer joins are involved
- Replace polymorphic associations with designs that support real foreign key constraints
- Add indexes deliberately based on query patterns rather than defensive over-indexing
- Separate application logic from database access so your ORM does not become an architectural trap
👤 Who is this book for?
- Backend developers who write SQL regularly but have not had a formal database education
- Application developers inheriting a legacy schema full of design decisions nobody can explain
- Junior DBAs who need a practical vocabulary for talking about design problems with their team
- Full-stack engineers who use an ORM but still write raw SQL for complex queries and reports
- Engineering leads doing code review who want a reference for explaining why a schema choice is problematic
Table of contents
-
01
Jaywalking
Storing multiple values as a comma-separated list in a single column looks convenient but makes joins, filtering, and referential integrity impossible. You learn the pattern, the damage, and how intersection tables solve it cleanly.
-
02
Naive Trees
Adjacency lists are the default way developers model hierarchies, but retrieving all descendants requires recursive queries that most developers cannot write. This chapter covers closure tables and nested sets as alternatives.
-
03
ID Required
Adding a surrogate primary key named 'id' to every table regardless of the data model leads to duplicate rows and lost natural keys. You learn when surrogate keys help and when they hide real design problems.
-
04
Keyless Entry
Skipping foreign key constraints to simplify inserts or avoid ORM friction leaves orphaned rows and silent data corruption. You see the real cost and how to use constraints without fighting your framework.
-
05
Entity-Attribute-Value
The EAV table trades a rigid schema for one that stores anything, but it breaks indexing, data typing, and every report you try to write. This chapter covers typed subtables and sparse columns as practical alternatives.
-
06
Polymorphic Associations
A single foreign key column that references rows in multiple tables cannot be enforced by the database. You learn how to redesign the relationship so that integrity is maintained without giving up flexibility.
-
07
Multicolumn Attributes
Spreading repeating attributes across numbered columns — phone1, phone2, phone3 — creates fixed limits and awkward queries. You replace the pattern with a dependent table and write simpler SQL as a result.
-
08
Metadata Tribbles
Splitting tables by date or range to manage volume causes the schema to grow unboundedly and makes cross-range queries painful. You learn when partitioning is the right tool and when it is not.
-
09
Rounding Errors and Other Query Traps
Implicit type coercions, floating-point arithmetic, and ambiguous GROUP BY clauses return wrong answers without raising errors. Each trap is demonstrated with a query that looks correct and is not, along with the fix.
-
10
Application-Layer Antipatterns
Magic Beans, Spaghetti Query, and other application-level mistakes turn the database into a bottleneck or a liability. You learn how to structure the boundary between application code and SQL so both stay maintainable.
Frequently asked questions
What SQL dialect does the book use?
The examples are written to be broadly portable across relational databases. The book calls out differences between MySQL, PostgreSQL, Oracle, and SQL Server where the platforms behave differently.
Do I need to be an experienced DBA to read this?
Intermediate SQL knowledge is enough. You should be comfortable writing SELECT, JOIN, and basic DDL statements. The book does not assume database administration experience.
Does the book cover NoSQL or document databases?
No. The book is focused entirely on relational databases and SQL. It does not address MongoDB, Redis, or similar systems.
Is a 2010 book still relevant today?
The antipatterns described are structural and logical, not tied to a specific database version. Schema design mistakes like EAV tables, missing foreign keys, and polymorphic associations are as common today as they were in 2010.
Is there companion code or a download to go with the book?
The book's publisher page at Pragmatic Bookshelf is the authoritative source for any supplementary materials. Check there for the most current information.
Who is this book not for?
If you are a complete SQL beginner who has not yet written JOIN queries, start with an introductory SQL title first. This book assumes you already write SQL regularly and want to write it better.
You might also like
New
New
T-SQL Fundamentals
A complete foundation in Transact-SQL for SQL Server and Azure SQL development
2023
View →
New
SQL Cookbook
Problem-Solution Recipes for SQL Across Multiple Database Platforms
by Anthony Molinaro, Robert de Graaf
2020
View →
