Books for software engineers and managers

SQL Antipatterns

Avoiding the Pitfalls of Database  Programming

by Bill Karwin

Categories:
Tech Lead,
Star Engineer

Be suspicious of claims of unlimited  flexibility

The Entity-Attribute-Value antipattern frequently comes with claims of unlimited flexibility.

Each time I’ve implemented this antipattern in my career, I thought I was being clever. Rather than defining the fields/attributes myself, end users could define the object attributes as they see fit. The possibilities are endless!

But frequently, I was sacrificing performance, queryability, and referential integrity while prematurely optimizing for flexibility, which often wasn’t required.

Look for arbitrary  limits

Imagine hearing that your content management database supports up to three tags per article. Arbitrary limits may signal an antipattern like Multicolumn Attributes, with unexpected inflexibility baked into your schema.

Beware the monster  query

In complex applications you often need to join many tables with complex logic. You write a giant query. That feels messy though, so you start refactoring it into subqueries or concatenated sections.

Each day for a week, you report in standup that you’re still working on this monster query. It’s a beast.

By the end of the week, your query feels more modular, but you also have no real idea what’s happening. Reviewing the code six months from now will confirm this.

Your query may perform better, but it’s also possibly an unmaintainable mess and you should consider breaking this up into several distinct queries.

Watch for tight coupling in Active  Record

By coupling data to model structures like Active Record does, we reduce testability and often push glue logic up into controllers, whereas it may be better positioned in a service or pure model.

SQL Antipatterns

How strongly do I recommend SQL Antipatterns?
8 / 10

SQL Antipatterns explains common mistakes in database design, plus a few application design errors that lead to database problems.

Readers will get the most from this book when they’ve personally made the mistake or at least experienced the consequences. In other words, SQL Antipatterns isn’t a book for SQL beginners but is perfect for experienced engineers.

Personally I’ve made each of these mistakes and it felt rewarding to see them named. I also found several improvements I can make today to applications I currently work on.

I would love for someone to write an extension to this book – Refactoring SQL Antipatterns at Scale. While SQL Antipatterns helps you identify structural mistakes and proposes better solutions, transitioning to those better solutions is often difficult when you’re dealing with millions or billions of records.