SQL Antipatterns: Jaywalking

Introduction

Antipatterns are techniques that are implemented to solve a specific problem but rather they create new problems.

Antipatterns are commonly used to solve common development problems. At first glance, they might seem like appropriate and clever solutions but instead, they create more unwanted coincidences than good ones. The term "antipattern" was first coined by Andrew Koenig in his paper "Patterns and Antipatterns" in 1995. The concept of design patterns inspired him. In his paper, he refers to antipatterns as

"An antipattern is just like a pattern, except that instead of a solution, it gives something that looks like a solution but isn’t one."

SQL Antipatterns refer to antipatterns that we use to solve database design problems.

Jaywalking

Assume You are working on a Ticketing system where each ticket is handled by one employee. when a Ticket is raised by the customer the customer care department assigns the ticket to the responsible Employee to resolve customer issues. The process is fairly simple however often customer raises a ticket where multiple people are needed to solve the issue. Since your system can only assign one employee to a ticket the work of the other employees is not reflected on their monthly KPI. So you are assigned to fix the issue. You always try to avoid joining since they can increase query time so you came up with a clever solution to use a comma-separated list of user identifiers instead of the single identifier it used before.

However, soon your boss came up to you complaining that they can't assign more than 5 users. If they try to add more. they get an error. After examining the error you understand the list of IDs has to fit in a string with maximum length, If the maximum length exceeds an exception arises.

This method of using a comma-separated list to avoid using an intersection table for a many-to-many relationship is called Jaywalking, because jaywalking is also an act of avoiding an intersection.

Objective

Initially, You have a Ticketing system where each ticket is assigned to one user. Storing the data of one user is fairly simple. You associate each ticket with a user using the UUID column in the Ticket table. So each user may handle many tickets but each ticket will be assigned to one user. As the product matures you understand that often it is necessary to assign tickets to multiple users.

Fig.1 - Initial Design

Antipattern: Format Comma Separate List

To minimize changes in the database and avoid joining you decide to alter the "assiged_user" field to a comma-separated list to store the list of assiged users.

Fig.2 - Antipattern Design

There are some legitimate uses of this pattern. like your system might need data in a comma-separated format and you don't need to access individual items in a list. Likewise, if your application receives data in comma-separated format you just need to store them as it is.

Solution

A better solution would be to create an intersection table "TicketAssignee" of the Ticket and User table that indicates the Many-to-Many relation between User and Ticket.

Fig.3 - Intersection Table Design

Reference

• SQL Antipatterns book chapter 2