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

Antipattern: Format Comma-Separated Lists

Let's explore Jaywalking antipattern in more detail.

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 ...