skip to content

A table stores each row's tags in one VARCHAR column as a comma-separated list such as 'sql,indexing,oltp'. What concretely breaks as the system grows, and what does the normalized design look like?

level: middleimportance: must knowfreq 60%

answer

  1. jaywalking = list in a column
  2. no FK, no index, LIKE hits nosql
  3. read-modify-write loses a tag
  4. junction table + composite PK
  5. opaque and never queried = the only excuse

basics

~20 s

You lose foreign keys, useful indexing and typing: substring matching finds false hits, per-tag counts need string surgery, adding one tag rewrites the whole string so concurrent updates lose each other, and typos accumulate. Fix: a child table with one row per (entity, tag).

solid answer

~60 s

Cramming a list into one column — sometimes called *jaywalking* — costs you everything the database was going to do for you. - **No referential integrity.** A foreign key can point at a column, not at a fragment of a string, so nothing prevents `sqll` or `SQL ` from appearing. - **No usable index.** A B-tree indexes the whole string, so "rows tagged sql" becomes a leading-wildcard scan — and the wildcard also matches `nosql`, so you write brittle delimiter-padded patterns. - **Read-modify-write races.** Adding a tag means reading the string, appending, writing it back. Two concurrent sessions each adding a different tag lose one of them. Two inserts into a child table would not collide. - **Type erasure and length limits.** A list of ids is text, so range comparisons and numeric ordering are gone, and the column has an arbitrary ceiling. - **Everything is awkward**: counts per tag, top-N tags, joining to a tag description, deleting one tag across all rows. The normalized design is a junction table: `entity_tag(entity_id, tag_id)` with a composite primary key and foreign keys to both sides, plus a `tag` table if tags have attributes.

code

sql · 14 lines
sql
-- before: wrong and unindexable
SELECT * FROM article WHERE tags LIKE '%sql%';   -- also matches 'nosql'

-- after
SELECT a.*
FROM article a
JOIN article_tag at ON at.article_id = a.id
JOIN tag t          ON t.id = at.tag_id
WHERE t.name = 'sql';

-- and counting per tag becomes trivial
SELECT t.name, COUNT(*)
FROM article_tag at JOIN tag t ON t.id = at.tag_id
GROUP BY t.name;

go deeper

for a junior

Say the column holds a list so it is not atomic, describe why searching with LIKE is wrong and slow, and give the child-table fix.

for a middle

Enumerate the concrete losses — foreign keys, indexes, typing, per-element aggregation — and write the junction table with its composite primary key and supporting index.

for a senior

Add the concurrency argument about read-modify-write lost updates, planner statistics being useless for element predicates, and a staged migration plan away from the column.

for a principal

Weigh the narrow legitimate case (small, opaque, never filtered, no integrity rules) against the cost of being wrong, and set a team-level rule for when a structured column type is allowed.

## The anti-pattern One column, many values, separated by a delimiter: `'sql,indexing,oltp'`, `'12,88,451'`, `'red|green|blue'`. It looks convenient because the whole collection travels with the row and the application already has a `split()` call handy. It is a first normal form violation: the cell is not a single value of its domain, it is an encoded collection, and it is a many-to-many relationship pretending to be a scalar attribute. ## What you actually lose **Integrity.** The single biggest loss. A foreign key constrains a *column value*; there is no way to say "every comma-separated fragment of this string must exist in `tag(id)`". So the column accumulates garbage: misspellings, case variants, trailing spaces, ids of rows that were deleted years ago. Every consumer must then defend itself, and the cleanup job that eventually runs is archaeology. **Search.** An index on `tags` sorts whole strings, so it can answer "tags equals exactly this string" and nothing else. "Which rows are tagged `sql`" becomes `tags LIKE '%sql%'`, which is a full scan and *also wrong*: it matches `nosql` and `sqlserver`. The usual patch is to store `',sql,indexing,'` with sentinel delimiters and search for `'%,sql,%'` — still a full scan, now with a fragile invariant that every writer must maintain. **Concurrency.** Adding one tag is a read-modify-write of the whole column. Two sessions that read `'a'` and write `'a,b'` and `'a,c'` respectively produce a lost update: one tag vanishes with no error. In a child table these are two independent `INSERT`s that both succeed. **Typing and limits.** Whatever the elements are — integers, dates, enum values — they become text. Ordering is lexicographic (`'10'` before `'9'`), range predicates are meaningless, and the column has an arbitrary maximum length that some row will eventually hit. **Query complexity.** "How many rows carry each tag" needs a string-splitting function and a lateral expansion instead of `GROUP BY`. "Rows with all of these three tags" and "rows with any of these three tags" are both trivial against a child table and both painful against a string. Deleting a retired tag everywhere becomes a mass string rewrite, taking a write lock on rows that had no logical change. **Cardinality and the planner.** Statistics are collected on the whole column. The optimizer has no idea how selective `'sql'` is, so estimates for any predicate over the list are guesses, and plans are correspondingly bad. ## The normalized design For a plain many-to-many: ``` article(id, title, …) tag(id, name UNIQUE) article_tag(article_id, tag_id, PRIMARY KEY (article_id, tag_id), FOREIGN KEY (article_id) REFERENCES article(id), FOREIGN KEY (tag_id) REFERENCES tag(id)) ``` What this buys, item for item against the losses above: - Referential integrity in both directions, plus `ON DELETE CASCADE` if you want tag removal to clean up automatically. - The composite primary key prevents duplicate tagging for free. - An index on `(tag_id, article_id)` makes "all articles with tag X" a range scan; the reverse index makes "all tags of article Y" one too. - Adding or removing one tag is a single-row `INSERT`/`DELETE` — no read-modify-write, no lost updates. - Counting, top-N, any/all-of queries are ordinary aggregation and joins. - Tags become an entity that can carry attributes: description, colour, created date, an owner. If tags are free-text and have no attributes, you can collapse the `tag` table and store `article_tag(article_id, tag VARCHAR(50))` with the composite key — still 1NF, still indexable, one join saved, at the cost of no central vocabulary and no cheap rename. ## The honest counter-argument A denormalized list is defensible in a narrow case: the collection is small, opaque, always read and written whole with its parent row, never filtered on, never joined, and never subject to integrity rules. A `preferences` blob or a display-only list of labels can qualify. Note how many conditions that is, and note that the first requirement to "find everything tagged X" invalidates all of them. If you do choose it, choose a structured type the engine understands rather than a hand-rolled delimiter, and write down why. ## Migrating away from it The usual path is expand-then-contract at the data level: create the child table, backfill it by splitting the strings (one pass, keeping a record of unparseable values), dual-write from the application for a period while readers move to the join, verify the two agree, then stop writing the string and drop the column in a later change. Expect the backfill to surface dirt the string column was hiding — that is the point.

  • Someone argues the comma-separated column is faster because it avoids a join. How do you evaluate that?
    It avoids a join only for the one access pattern of fetching a row and displaying its list. For every other pattern it is dramatically slower, because filtering by an element becomes a full scan with a wildcard predicate while the junction table answers it with an index range scan. It also trades a bounded join cost for unbounded correctness risk, since nothing enforces that the fragments are valid.
  • How would you migrate a live table away from a comma-separated tags column?
    Create the junction table and backfill it by splitting the existing strings in one pass, recording rows that fail to parse. Then have the application write both places while readers are switched over to the join, and compare the two representations until they agree. Finally stop writing the string and drop the column in a separate, later migration, so no single deploy is both additive and destructive.
  • Does storing the list as a native array or JSON column instead of a delimited string fix the problem?
    It fixes the parsing and typing half: the engine understands the structure, can often index elements, and the application no longer hand-rolls delimiters. It does not restore referential integrity, since a foreign key still cannot reference an element, and it still leaves updates as whole-value rewrites in most engines. Strictly the table is still outside first normal form, so it is a considered trade-off rather than a fix.

It is like writing all your contacts on one line of a paper address book: you can still read it, but you cannot alphabetise it, cross one out without rewriting the line, or tell whether 'Jon' and 'John' are the same person.

saying these in an interview costs you the question

  • Claiming LIKE '%tag%' is fine because the table is small — it hard-codes a scan and matches substrings such as nosql
  • Believing a plain index on the list column makes element lookups fast
  • Not noticing that adding one element is a read-modify-write that can lose a concurrent update
  • Assuming a junction table is 'more joins so slower', without considering the access patterns it makes indexable
  • Treating a JSON or array column as fully equivalent to normalization, ignoring the missing foreign key

context

resolving links…