Books for software engineers and managers

SQL Antipatterns

SQL Antipatterns

Avoiding the Pitfalls of Database  Programming

by Bill Karwin

Categories:
Tech Lead,
Star Engineer

How strongly do I recommend SQL Antipatterns?
8 / 10

Review of SQL Antipatterns

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.



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 baked into to your database table  structures

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.

Backing out of these inflexible structures gets harder as more data gets added to your system because data migrations become more expensive and error prone.

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.

Another downside to having monster SQL queries is that splitting your application from monolith to microservices will be harder because data is being fetched across multiple problem domains.

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