DEV Community: Monir Hossain

SQL Antipatterns: Jaywalking

Monir Hossain — Sun, 19 Nov 2023 12:19:42 +0000

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

This might seem like a good solution since you avoiding adding new tables and columns but let's look at some of the performance and integrity issues this design will suffer from.

Querying Ticket for a Specific User: It is difficult to query Tickets for a specific user when foreign keys are combined into a single field. Straight Forward equality check will not work. You have to use pattern matching and will not get the benefit of indexing. Pattern matching expression is different for each vendor, so your query will not be vendor-natural.
Querying User For a Given Ticket: Likewise It is costly to join User and Ticket Table when User IDs are represented as a comma-separated list. You have to use awkward regular expressions to match rows in different tables.
Making Aggregate Query: Aggregate functions are designed to work over a list of rows, not on comma-separated list, so It will be tricky to use them in code. moreover, these tricks will make queries less clear, time-consuming to develop, and hard to debug.
Updating User for a Specific Ticket: To assign a ticket you can contact the ID end of the string but the list will not be in sorted order. To Delete a user from a ticket you need to perform two queries one to fetch the current comman-seperated ID list and another to update the row with a new list.
Validating User IDs: Since IDs do not refer to any field it is possible to add garbage data and lose data integrity.
Choosing a Separator Character: It is also necessery to choose separator cautiously since the separator can not appear in any IDs.
List Length Limitation: How many IDs can be added to the list is not fixed since It is based on ID size. If the maximum length is 30 and each ID consist of 2 characters you will manage to add boos10 User to a ticket but if the size of the ID is not fixed the number above will vary.

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

Let's see how the intersection table solves all the problems we see in Antipattern.

Querying Tickets by User and the Other Way Around: With intersection table query on Tickets and TicketAssigneee table is much straight forward. With indexing this query will perform better than the query on a common-separated string.
Making Aggregate Queries: Aggregate functions can be used to generate more sophisticated reports.
Updating Ticket Assignee for a Specific Ticket: Add and Delete Ticket Assignee can be possible by inserting and deleting rows in TicketAssignee Table.
Validating User IDs: Invalid user ID add is not possible since ForeignKey validates the value against valid entries.
Choosing a Separator Character: Choosing a separator character is not necessary since each entry is stored in separate rows.
List Length Limitations Since entries are stored in separate rows. The list is only limited by the maximum number of rows that can physically exist in one table.

Reference

SQL Antipatterns book chapter 2

Design Patterns: Adapter Design Pattern

Monir Hossain — Sat, 23 Sep 2023 18:08:51 +0000

Introduction

The adapter Design pattern also known as the wrapper design pattern converts the interface of a class into another interface the client expects. Adapters let classes work together that couldn't otherwise because of incompatible interfaces. If the adaptee changes over time the adapter should also encapsulate those changes, This ensures that the client doesn't require modification whenever it needs to interact with a different interface.

It is easy to understand because we frequently see them in our day-to-day lives. Imagine you are traveling to a foreign country and you need to charge your electronic devices but the electronic outlet in the foreign country uses a different plug type and voltage compared to your home country. In this situation, you need an adapter to bridge the gap between your device's plug and the foreign country's outlet. The adapter allows you to connect your device to the foreign electrical system, ensuring that it receives the correct voltage and fits into the different plug types.

Problem

Assume you are hired as a developer in an E-commerce site and now you are responsible for integrating multiple payment gateway in your system like PayPal, Stax, etc also if necessary the product owner would like to add some more payment gateway in his system. However, each payment gateway has a different interface for payment processing. but we need a standardized interface for processing payments. It is possible to solve this problem without a standardized interface. but this will make the codebase messy and less future-proof.

Solution

To address this problem we can use the adapter design pattern. we will implement an adapter for each payment gateway. These adapters will bridge the gap between the diverse payment gateway interface and your standard payment processing interface.

Now to implement the adapter design pattern there are two kinds of adapter based on inheritence and object composition.

Class Adapter: The class adapter is based on multiple inheritance. where adapter will inherit both the targeted class and adaptee to make the interface compatible with the client interface. class adapter implementation is only possible in programming languages that support multiple inheritance like C++, Python, etc.
Object Adapter: The object adapter makes use of the object composition principle. where the adapter will implement the targeted interface and wrap the adaptee class for compatibility. object adapter can be implemented in all popular programming languages.

Fig.1 - Class Adapter Diagram

Fig.2 - Object Adapter Diagram

Sample Code

It is time to see the adapter in action

import abc


class StandardPGInterface(metaclass=abc.ABCMeta):
    @abc.abstractmethod
    def process_payment(self, amount, *args, **kwargs):
        raise NotImplemented


class PayPalPG:
    def initiate_payment(self, amount):
        # Implement Payment code
        pass


class StaxPG:
    def make_payment(self, amount):
        # Implement Payment code
        pass


class PayPalPGAdapter(StandardPGInterface):
    def __init__(self):
        self.paypal = PayPalPG()

    def process_payment(self, amount):
        self.paypal.initiate_payment(amount=amount)


class StaxPGAdapter(StandardPGInterface):
    def __init__(self):
        self.stax = StaxPG()

    def process_payment(self, amount):
        self.stax.make_payment(amount=amount)


if __name__ == "__main__":
    paypal = PayPalPGAdapter()
    paypal.process_payment(amount=100)
    stax = StaxPGAdapter()
    stax.process_payment(amount=100)

Applicability

Use the adapter pattern when:

You want to use an existing class but its interface does not match the one you need.
You aim to develop a reusable class that can collaborate with unrelated and unforeseen classes.
(object adapter only) You need to use several existing subclasses, but it's impractical to adapt their interface by subclassing every one. An object adapter can adapt the interface of its parent class.

The trade-off between class and object adapter

Class Adapter:

Class adapter won't work when we want to adapt a class and all its subclasses.
Class adapters can override the behavior of the adaptee. since the class adapter is a subclass of adaptee.

Object Adapter:

lets a single adapter have many adaptees, that is the adaptee and all of its subclasses.

Pros and Cons

Pros

Maintain Single Responsibility Principle. You can divide the interface from the main business logic of the program.
Maintain Open/Closed Principle. You can introduce new types of adapters without breaking the client code.

Cons

Overall code complexity increases because of new interfaces and classes.

Resources

"Head First Design Patterns" by Eric Freeman, Bert Bates, Kathy Sierra, Elisabeth Robson, https://www.amazon.com/Head-First-Design-Patterns-Brain-Friendly/dp/0596007124
"Design Patterns: Elements of Reusable Object-Oriented Software" by Gamma Erich, Helm Richard, Johnson Ralph, Vlissides John, https://www.amazon.com/Design-Patterns-Object-Oriented-Addison-Wesley-Professional-ebook/dp/B000SEIBB8
Refactoring Guru article on Adapter Design Pattern, https://refactoring.guru/design-patterns/adapter

Design Patterns: Introduction

Monir Hossain — Wed, 20 Sep 2023 08:56:28 +0000

What is a Design Pattern

A design pattern is a reusable solution to commonly occurring problems within a given context. It is not the finished design that can be applied directly to source and machine code. Instead, it provides a description and template for how to solve the problem that can be used in many different situations. It is the best practice that programmers can use to solve common problems when designing an application.

The use of successful designs and architectures is facilitated by design patterns. The use of established principles as design techniques makes them more readily available to developers of new systems. In other words, design patterns speed up the process of getting a design right.

Fig.1 - Photo by fancycrave1

Design Pattern Structure

A design pattern has four essential elements.

Pattern Name
Problem
Solution
Consequence

The pattern name is a handle we use to describe the design problem, its solutions, and its consequences in a word or two. It lets us design at a higher level of abstraction. It makes it easier to think about design and to communicate them and their trade-offs to others.

The problem describes when to apply the pattern. It explains the problem and its context. Sometimes the pain will include a list of conditions that must be met before it makes sense to apply the pattern.

The solution describes the elements that make up the design, their relationships, responsibilities, and collaborations. The solution doesn't describe a particular concrete design or implementation Instead, It provides an abstract description of a design problem and how a general arrangement of elements (classes and objects in our case) solves it.

The consequences are the results and trade-offs of applying the pattern. Though consequences are often unvoiced when we describe design decisions, they are critical for evaluating design alternatives and understanding the cost and benefit of applying the pattern.

The Catalog of Design Patterns

I've listed some well-known design patterns along with brief descriptions from the GOF Design Patterns book

Creational Patterns
- Factory Method creates an interface for object creation but leaves it up to subclasses to determine which class should be instantiated.
- Abstract Factory offers a way to define a blueprint for producing groups of interconnected objects without requiring specific details about the actual classes of those objects.
- Builder decouples the assembly of a complex object from its structure, allowing the same assembly process to generate various representations.
- Prototype Specify the kinds of objects to create using a prototypical instance, and create new objects by copying this prototype.
- Singleton guarantees that a class has just a single instance and offers a universal means to access that instance.
Structural Patterns
- Adapter transforms a class's interface into a different interface that matches the expectations of its clients.
- Bridge separates an abstraction from how it's implemented so that changes in one don't affect the other, allowing them to evolve independently.
- Composite combines individual objects into hierarchical tree structures to represent relationships between parts and wholes.
- Decorator dynamically adds extra responsibilities to an object.
- Facad offers a single, simplified interface that encompasses a group of interfaces within a subsystem.
- Flyweight uses sharing to support large numbers of fine-grained objects efficiently.
- Proxy offers a stand-in or substitute for another object, controlling and regulating access to that object.
Behavioral Patterns
- Chain of Responsibility prevents tight connections between the sender of a request and its recipient by providing multiple objects with the opportunity to handle the request.
- Command wraps a request as an object, allowing you to customize clients with various requests, queue or record requests, and enable reversible actions.
- Iterator offers a method to access the elements of a collective object sequentially while hiding the underlying structure of that object.
- Memento Without breaching encapsulation, record and externalize an object's internal state to enable the object's restoration to that state at a later time.
- Observer establishes a relationship of one-to-many between objects, ensuring that when one object changes state, all of its dependents are automatically notified and updated.
- State enables an object to modify its actions or behavior in response to changes in its internal state.
- Strategy defines Design Patterna group of algorithms, encapsulates each of them separately, and enables them to be switched out interchangeably.
- Template Method creates the basic structure of an algorithm within a method, delegating specific steps to subclasses for customization.
- Visitor represents an operation to be performed on the elements of an object structure.

Fig.2 Relation between design patterns

Conclusion

A comprehensive discussion on the use of design patterns should also include some guidance on what not to do when employing them. Design patterns are not focused on designs like straightforward data structures such as linked lists and hash tables that can be represented in classes and directly reused. Likewise, they are not intricate, application-specific designs that pertain to an entire application or subsystem.

It's important to apply a design pattern only when you genuinely require the flexibility it offers. The Consequences sections become especially valuable when assessing the advantages and drawbacks of a pattern.