Search⌘ K
AI Features
Home
Courses
SQL Antipatterns: Avoiding the Pitfalls of Database Programming

Antipattern: Format Comma-Separated Lists

Understand the pitfalls of storing multiple values in a single VARCHAR column separated by commas. Explore the challenges such as inefficient queries, difficulty in updating, inability to use indexes, and validation issues. Learn why this design is an antipattern and how it impacts data integrity and performance.

We'll cover the following...

Returning to our example, to minimize changes to the database structure, you decide to redefine the account_id column as a VARCHAR so you can list multiple account IDs in that column, separated by commas.

MySQL
CREATE TABLE Products (
  product_id SERIAL PRIMARY KEY,
  product_name VARCHAR(1000),
  account_id VARCHAR(30) -- comma-separated list
  -- . . .
);
INSERT INTO Products (product_id, product_name, account_id)
     VALUES (DEFAULT, 'Visual TurboBuilder', '12,34');

This seems like a win because you’ve created no additional tables or columns; you’ve changed only one column’s data type. However, let’s look at the performance and data integrity problems this table design suffers from.

Querying products for a specific account

Although a foreign key can handle the insert, update, and delete functions, the queries are difficult if all the foreign keys are combined into a single field. Thus, you can no longer use equality; instead, you have to use a test against some kind of pattern. For example, MySQL lets you write something like the following to find all the products for account 12:

MySQL
SELECT * FROM Products WHERE account_id REGEXP '[[:<:]]12[[:>:]]';

Note: We can also use some other queries for data retrieval. For example, you can check it for accounts 34 or 23.

Pattern-matching expressions may ...