Search⌘ K
AI Features
Home
Courses
SQL Antipatterns: Avoiding the Pitfalls of Database Programming

Antipattern: Create Multiple Columns

Explore the problems caused by creating multiple columns to store multiple values in SQL attributes. Understand how this antipattern complicates searching, updating, and maintaining data. Learn the challenges with query complexity, ensuring uniqueness, handling nulls, and expanding columns. Gain insights into why storing single values per column is crucial for effective, scalable database design and how to avoid this common pitfall.

We'll cover the following...

We still have to account for multiple values in the attribute, but we know that the new solution must store only a single value in each column. It may seem natural to create multiple columns in this table, each containing a single tag.

MySQL
CREATE TABLE Bugs (
  bug_id SERIAL PRIMARY KEY,
  description VARCHAR(1000),
  tag1 VARCHAR(20),
  tag2 VARCHAR(20),
  tag3 VARCHAR(20)
);

As we assign tags to a given bug, we’d put values in one of these three columns. Unused columns would remain null.

MySQL
UPDATE Bugs SET tag2 = 'performance' WHERE bug_id = 3456;

Let’s try to retrieve the Bugs table data after updating the value of tag2. Press “RUN” in the following playground and see the output.

MySQL
UPDATE Bugs SET tag2 = 'performance' WHERE bug_id = 3456;
SELECT * FROM Bugs WHERE bug_id = 3456;

Tasks we could do easily with a normal attribute are now more complex.

Searching for values

When searching for bugs with a given tag, we must search all three columns because the tag string could occupy any of these columns.

If we’re searching for a given value across multiple columns, it is a clue that the multiple columns should be stored as a single logical attribute.

For example, to retrieve bugs that reference ...