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.