SQL Antipatterns

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.

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.

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.

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.