O'Reilly logo

SQL Antipatterns by Bill Karwin

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Antipattern: Create Multiple Columns

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

Multi-Column/anti/create-table.sql
 
CREATE​ ​TABLE​ Bugs (
 
bug_id SERIAL ​PRIMARY​ ​KEY​,
 
description ​VARCHAR​(1000),
 
tag1 ​VARCHAR​(20),
 
tag2 ​VARCHAR​(20),
 
tag3 ​VARCHAR​(20)
 
);

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

Multi-Column/anti/update.sql
 
UPDATE​ Bugs ​SET​ tag2 = ​'performance'​ ​WHERE​ bug_id = 3456;

bug_id

description

tag1

tag2

tag3

1234

Crashes while saving

crash

NULL

NULL

3456

Increase performance ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required