An antipattern is an attempt to solve a problem that often causes other problems at the same time. Although anti-patterns are widely practiced in different forms, they have some commonality.

An antipattern is an attempt to solve a problem that often causes other problems at the same time. Although anti-patterns are widely practiced in different forms, they have some commonality.

jaywalking

Changing a database field that stores a single user id to a comma-separated list of user ids is not really flexible enough. Only characters of a specified size can be stored.

Programmers often use comma-separated lists to avoid creating cross-tables in many-to-many relationships, an approach the authors define as an anti-pattern called jaywalking.

Target: Stores multi-valued attributes

Product, account, and contact many-to-many relationships

A collection of related data stored in a column.

Anti-pattern: Formatted comma separated list

It’s constrained by the list length.

The data that can be stored in a structure such as VARCHAR(30) depends on the length of each entry. If each entry is only two characters long, you can store 10 entries, including commas.

The solution

Create a crosstab

CREATE TABLE Contacts(
	product_id BIGINT UNSIGNED NOT NULL,
	account_id BIGINT UNSIGNED NOT NULL,
	PRIMARY KEY (product_id, account_id),
	FOREIGN KEY (product_id) REFERENCES PRODUCTS(product_id),
	FOREIGN KEY (account_id) REFERENCES Accounts(account_id)
);
Copy the code