Discover more from OpenLampTech

The LAMP stack and the PHP technologies and frameworks it runs. Code, content, and community for developers.

Book Recommendation - SQL Antipatterns

Find out why SQL Antipatterns: Avoiding the Pitfalls of Database Programming by Bill Karwin is one of my favorite SQL books...

Mar 1, 2022

In this book recommendation, I’m sharing my thoughts on one of my favorite SQL books for developers, SQL Antipatterns: Avoiding the Pitfalls of Database Programming by Bill Karwin. I’m labeling this article a book recommendation instead of a book review because a book review would indicate that I have finished the book.

In my opinion, this book is not a front-to-back, ‘one and done’ type of book, but more so a resource you reference as your development progresses or you encounter any of the covered antipatterns.

Yes, you can read this book front-to-back.

However, I think only over time are the lesson within truly learned and I can assure you, I am still learning them.

Regardless of your development forte, if you are working with SQL databases, I highly recommend you have this book in your library.

📝 Note: This blog post is not a paid review. There are no affiliate links or commissions included or involved in my recommendation and brief overview pertaining to the book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming. I write this article of my own accord with my own opinions, only to share what I think is a fantastic learning resource for anyone who wants to maximize SQL use and skills.

SQL Antipatterns: Avoiding the Pitfalls of Database Programming (not an affiliate link) is so good, I have a print edition on my desk and a digital Kindle version.

Yes, it is that good.

Support OpenLampTech, my blog, and my work with a coffee if you would like.☕

There are 4 parts in the SQL Antipatterns book, each containing several chapters. I’ll summarize my take on an antipattern from 1 chapter for each of the 4 parts and leave the rest to your curiosity.

I do hope you get this book. There is so much to learn from it.

Part 1: Entity-Attribute Value

The Entity-Attribute Value chapter focuses on the antipattern of using a generic attribute table - often termed EAV - to support a variable number of attributes. This table typically consists of 3 columns:

The entity
The attribute
The value

EAV table design poses a few problems that might not be readily apparent:

Searching for data for a specific attribute - what would normally be a column in a table - is now an attribute string.
Can’t enforce mandatory attributes like you can columns without writing application code instead of database design.
Unable to use SQL data types because the attributes value column is likely a string type to allow for a wide range of possible attributes.
Enforcing referential integrity with a conventional FOREIGN KEY on the value column is limited or in some cases, unavailable.
Difficult to mandate consistent attribute names - E.g., is it ‘f_name’, ‘first_name’, or ‘firstName’…
Querying an entire row worth of data is cumbersome since each attribute is stored on a separate row in the EAV table, requiring multiple OUTER JOINs.

Furthermore, we see example queries, demonstrating some of the difficulties in working with the EAV antipattern.

This chapter also covers alternative design patterns you can employ should you feel you need to use EAV. However, their discussion and coverage are beyond the scope of this article.

To be clear, I have not had to personally work with any EAV-like tables in my development, so I have no first-hand knowledge about them.

Part 2: 31 Flavors

Restricting a column’s values to a specific predetermined set allows you to control the data that can be stored in said column.

SQL CHECK constraints are one way to limit values a column can store, but, introduce some limitations requiring extra work in other areas of the application development lifecycle. In those instances where you need a list of the allowed column values, oftentimes you must query one of the system tables for the column definition information.

“The queries needed to report check constraints, domains, or UDTs are progressively more complex. Most people choose the better part of valor and manually maintain a parallel list of values in application code. This is an easy way for bugs to affect your project as application data becomes out of sync with the database metadata.” Bill Karwin

In the event, you need to remove an existing allowable value, or include a new value in the columns’ definition, you must redefine the column itself using ALTER TABLE. Not to mention, if you remove a previously allowed value, what about any existing data in the database that depends on or uses that value?

Supporting multiple database systems is difficult when the column metadata includes the allowable column values as these (metadata) are not universal across the different SQL vendors.

In order to avoid many of these problems, the author further discusses using a lookup table storing the predefined set of allowable values. This essentially defines the values in data instead of metadata.

Using a FOREIGN KEY definition in the joining table ensures the column must contain a value that is stored in the lookup table.

Plus, querying the lookup table for all of the allowable values is a far simpler SELECT query than those against the system tables, which often also require additional parsing to get the data you need.

“Use metadata when validating against a fixed set of values. Use data when validating against a fluid set of values.” Bill Karwin

Part 3: Poor Man's Search Engine

Full-text search functionality is supported differently across the many database vendors. True, SQL does provide some pattern matching functionality with the LIKE operator and other forms of REGEXP functionality.

However, LIKE is not that efficient, and can return matches that are very similar to, but not related to the initial intended search term.

In this chapter, the author summarizes many of the available 3rd party options (at the time of the books’ writing) in addition to, proprietary features available as part of the popular database brands, for efficient full-text search functionality.

In this chapter, we further learn how to ‘roll your own’ search functionality in SQL by designing an inverted index.

Prior to reading SQL Antipatterns, I had never heard of an inverted index. Nor have knowingly used one. It is essentially one big list of keywords someone might search for.

The inverted index consists of a keywords table and an intersection table which forms a many-to-many relationship for searches.

The heartbeat of the whole system (termed loosely here) is using a stored procedure to handle the inserts for keywords that haven't previously been searched for, along with performing the searches.

It's actually pretty slick.

But, I won’t get too much into the weeds on the stored procedure SQL code, and will instead leave that up to you as you explore this chapter in the book.

Any new keyword data is inserted into the many-to-many (intersection) table using a TRIGGER when searches are made for them.

My take on the inverted index is that once you have taken the initial hit for a costly search, the searched keyword is stored in the keyword table, and any future searchers can benefit from any of the previous searches.

Part 4: See No Evil

On my initial read of this chapter, I felt as if the author was speaking directly to me.

“Everyone wants to write elegant code. That is, we want to do cool work with little code. The cooler the work is and the less code it takes us, the greater the ratio of elegance. If we can’t make our work cooler, it stands to reason that at least we can improve the elegance ratio of coolness to code volume by doing the same work with less code.” Bill Karwin

Something I had to learn the hard way was checking the return value of a Database API call in my application code. Since I work with PHP, this normally consists of either PDO or MySQLi.

In this chapter, the author covers an example of a Database API call that should have the function and method return values checked in order to handle errors and/or exceptions more gracefully.

One point in this chapter that I really feel like I have a head start on is, checking the SQL itself for any errors instead of checking the application code that creates the SQL.

With a dynamic scripting language such as PHP, you can build up some powerful SQL query strings and I use this workflow quite often myself with the trim() family of functions and string concatenation.

However, this approach to building up dynamic SQL strings with application code introduces an opportunity for something as simple as missing whitespace or a leftover comma, which can create bugs that are often difficult to track down.

Just as the author suggests, check the actual SQL code first. In doing so, you can often spot issues and bugs more quickly than trying to picture the SQL the application code is building.

Final thoughts

I want to leave you with this: I cannot do this book any justice with one recommendation article. There is so much great content in this book that anyone from an entry-level developer to a more seasoned senior-level or lead, can learn something and further their development growth.

Thank you for reading!

Do you need to learn MySQL? Just starting out and confused about how to query a table and get the data you need? I am creating premium MySQL Beginners content for this specific reason. To help those who want to learn the basics of MySQL but don't know where to start. Learn more about my premium MySQL blog posts and content as I develop and release them and get a MySQL tip each week as well.

Tell me more!

Visit my blog Digital Owl’s Prose, where I write regularly on CodeIgniter, PHP, and SQL. I’m also active on Medium, LinkedIn, Twitter, and Instagram.

Thank you for reading OpenLampTech. This post is public so feel free to share it. Thank you! 🙏

Are you thinking of starting up a blog? I use WordPress for my blog, Digital Owl’s Prose. Let’s both save money on the plans offered. 💸
Grab a Gmail HTML Email Signature template from my Etsy shop and make your emails pop and stand out. ✉️
Need hosting for your next web application or WordPress site? I use and highly recommend Hostinger. They have great pricing and service.
I enjoy reading Refind: The essence of the web, every morning in your inbox. Subscribe for free. Help me get a premium subscription by signing up yourself with my referral link.
Grab a free pack of mobile Creator wallpapers with my referral link.
Just getting started or wanting to learn MySQL? Find out about my premium blog posts and MySQL Beginner Series here.

Want to support this newsletter and my work? Drop some spare change in my Tip Jar.💰

Tip Jar

Disclosure: Some of the services and products links in this email are affiliate links. At no additional cost to you, should you make a purchase by clicking through one of them, I will receive a commission.

Cover image photo credits: Image by mohamed Hassan from Pixabay