SQL Antipatterns: Avoiding the Pitfalls of Database Programming/

...

/

Antipattern: Create Multiple Columns

Antipattern: Create Multiple Columns

Let's create multiple columns for multi-column attributes.

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.

Press + to interact
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.

Press + to interact
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.

Press + to interact
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 performance, we can use a ...