SQL Antipatterns Ch02

5th July 2017 at 10:07am
SQL Antipatterns

Chapter 2: Jaywalking

  • Objective: Store Multivalue Attributes
  • Antipattern: Format Comma-Separated Lists
CREATE TABLE Products (
product_id
 SERIAL PRIMARY KEY,
product_name VARCHAR(1000),
account_id
 VARCHAR(100), -- comma-separated list
-- . . .
);

This chapter explains why using a comma-separated lists to store multivalue data is a bad practice by providing many case:

  • Querying Products for a Specific Account
    • SELECT * FROM Products WHERE account_id REGEXP '[[:<:]]12[[:>:]]' ;
  • Querying Accounts for a Given Product
  • Querying Products for a Specific Account
  • Making Aggregate Queries
  • ...

The solution is to create an intersection table.

The best part of this chapter is that the author is telling you how to think about a specific design is suitable or not. By providing use cases, you can compare two choices of design and consider which one is better.