SQL Help

Stop storing comma-separated lists: normalise with a junction table

Updated August 2026 · First published 2006 by David J. Lake

It's tempting: a property listing has features, so you store them in one column — 'pool,garage,3 bedrooms'. One table, no joins, done. Then the first real search request arrives and the design falls apart. This is the single most common schema mistake there is, and the fix — a junction table — is one of the most useful patterns in SQL.

Why the list column fails

CREATE TABLE properties (
  property_id INT AUTO_INCREMENT PRIMARY KEY,
  address     VARCHAR(80),
  features    TEXT
);

INSERT INTO properties (address, features) VALUES
('82 Fairington', 'pool,fireplace,garage,4 bedrooms,3 bathrooms'),
('10 Primrose',   'fireplace,two storey,garage,3 bedrooms,3 bathrooms'),
('2 Frontenac',   'carport,3 bedrooms,2 bathrooms,bungalow'),
('786 Ossington', '3 bedrooms,1 bathroom,two storey');

To find properties with 2 bathrooms you're forced into:

SELECT * FROM properties
WHERE features LIKE '%2 bathrooms%';

Three problems, each fatal on its own:

• No index can help. A LIKE pattern starting with % means a full table scan, every search, forever.
• Substring matching lies. Searching for garage happily matches no garage; 1 bathroom matches 21 bathrooms. (MySQL's FIND_IN_SET() fixes the false-match problem but not the index problem.)
• Counting is impossible. "Show homes with at least 3 of these 5 features" has no sane answer against a string column.

The normalised design

Three tables: the things, the features, and a junction table connecting them (you'll also hear "linking table" or "many-to-many table"):

CREATE TABLE properties (
  property_id INT AUTO_INCREMENT PRIMARY KEY,
  address     VARCHAR(80)
);

CREATE TABLE features (
  feature_id INT AUTO_INCREMENT PRIMARY KEY,
  feature    VARCHAR(40) NOT NULL UNIQUE
);

CREATE TABLE property_features (
  property_id INT NOT NULL,
  feature_id  INT NOT NULL,
  PRIMARY KEY (property_id, feature_id)
);

The two-column primary key both prevents listing the same feature twice for one property and serves as the index for "all features of property X". Add a second index the other way round — INDEX (feature_id, property_id) — and "all properties with feature Y" is indexed too. That's the entire performance story the list column couldn't offer.

Querying it

Rebuild the readable list at display time with GROUP_CONCAT:

SELECT p.property_id, p.address,
       GROUP_CONCAT(f.feature ORDER BY f.feature SEPARATOR ', ') AS features
FROM properties p
JOIN property_features pf ON pf.property_id = p.property_id
JOIN features f           ON f.feature_id  = pf.feature_id
GROUP BY p.property_id, p.address;

All of several features — the counting trick. Filter to the features you care about, group per property, and demand the full count:

SELECT p.property_id, p.address
FROM properties p
JOIN property_features pf ON pf.property_id = p.property_id
JOIN features f           ON f.feature_id  = pf.feature_id
WHERE f.feature IN ('3 bathrooms', 'two storey')
GROUP BY p.property_id, p.address
HAVING COUNT(*) = 2;          -- must match both

At least 3 of 5 wanted features — same query, different bar:

WHERE f.feature IN ('3 bathrooms','two storey','pool','garage','fireplace')
GROUP BY p.property_id, p.address
HAVING COUNT(*) >= 3;

Weighted matching — make some features count more:

HAVING SUM(CASE WHEN f.feature = 'pool' THEN 2 ELSE 1 END) >= 3;

None of these are expressible against a comma-separated column. All of them are one-line variations once the data is normalised.

"But what about JSON columns?"

Modern MySQL will let you store ["pool","garage"] in a JSON column and even index it (multi-valued indexes, MEMBER OF). That's genuinely useful for ragged, schema-less attributes you never filter on relationally. But if the values are a fixed vocabulary you search, count and join on — features, tags, categories — the junction table remains the right tool: simpler queries, real foreign keys, and statistics the optimiser actually understands. Reach for JSON deliberately, not as a comfier way to avoid a join.