Model Many-to-Many Relationships in MySQL

A many-to-many relationship belongs in a relationship table. I once tried to avoid that table by storing related IDs as comma-separated text. The idea looked attractive for archival data: one less table, fewer foreign keys, and a compact row containing everything I needed.

In practice, the table did not disappear. Its responsibilities moved into string parsing, application code, and queries that MySQL could not index efficiently. The CSV column also lost referential integrity, uniqueness, useful statistics, and straightforward updates. A conventional junction table is both simpler and faster once the data is queried as a relationship.

Model the Relationship Explicitly

Suppose articles can have many references and each reference can belong to many articles. Both entity tables keep their own identity; the junction table stores each assignment exactly once.

CREATE TABLE article (
  article_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  title VARCHAR(255) NOT NULL,
  PRIMARY KEY (article_id)
) ENGINE=InnoDB;

CREATE TABLE reference_item (
  reference_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  label VARCHAR(255) NOT NULL,
  PRIMARY KEY (reference_id)
) ENGINE=InnoDB;

CREATE TABLE article_reference (
  article_id BIGINT UNSIGNED NOT NULL,
  reference_id BIGINT UNSIGNED NOT NULL,
  attached_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (article_id, reference_id),
  KEY ix_article_reference_reverse (reference_id, article_id),
  CONSTRAINT fk_article_reference_article
    FOREIGN KEY (article_id) REFERENCES article (article_id)
    ON DELETE CASCADE,
  CONSTRAINT fk_article_reference_reference
    FOREIGN KEY (reference_id) REFERENCES reference_item (reference_id)
    ON DELETE RESTRICT
) ENGINE=InnoDB;

The composite primary key prevents duplicate assignments and supports every lookup beginning with article_id. The reverse index supports the opposite direction. Foreign keys reject unknown identifiers and define what deletion means instead of leaving stale numbers embedded in text.

A relationship may also carry data of its own. Position, role, source, validity period, and creation time belong in the junction row when they describe the assignment rather than either entity.

Add and Remove Assignments

Insert one row per assignment. If repeated requests are expected and should be idempotent, use an upsert whose duplicate branch leaves the existing relationship unchanged:

INSERT INTO article_reference (article_id, reference_id)
VALUES (?, ?) AS incoming
ON DUPLICATE KEY UPDATE
  reference_id = incoming.reference_id;

This does not hide foreign-key violations or other errors. An unknown article or reference still fails. Removing an assignment is equally direct:

DELETE FROM article_reference
WHERE article_id = ?
  AND reference_id = ?;

Updating a CSV string safely would require parsing it, preventing duplicates, handling concurrent writers, and serializing the result again. The relationship row lets InnoDB enforce concurrency and identity with its normal locking and unique-index rules.

Query in Either Direction

Find all references attached to one article:

SELECT r.reference_id, r.label, ar.attached_at
FROM article_reference AS ar
JOIN reference_item AS r
  ON r.reference_id = ar.reference_id
WHERE ar.article_id = ?
ORDER BY ar.attached_at, r.reference_id;

Or find every article using one reference:

SELECT a.article_id, a.title
FROM article_reference AS ar
JOIN article AS a
  ON a.article_id = ar.article_id
WHERE ar.reference_id = ?
ORDER BY a.article_id;

Both access paths start with an index. By contrast, FIND_IN_SET(?, ref_csv) must inspect and parse the stored string row by row. Textual membership also makes cardinality estimates less useful to the optimizer.

Why CSV IDs Fail as a Relationship

• No referential integrity: MySQL cannot attach a foreign key to each token inside a string.
• No indexed membership: a normal index cannot locate an ID at an arbitrary position in CSV text.
• No reliable uniqueness: values such as 2,2 require custom duplicate handling.
• Ambiguous representation: whitespace, empty tokens, signs, leading zeros, and malformed values need rules outside the schema.
• Race-prone updates: two read-modify-write operations can overwrite each other's additions.
• Difficult maintenance: deleting or replacing one referenced ID requires rewriting many strings.

FIND_IN_SET() also treats a comma as structure, so it is not a general CSV parser. Proper CSV permits quoted fields, escaped quotes, and delimiters inside values. A list of numeric IDs avoids some syntax problems but not the relational ones.

Migrate an Existing CSV Column

Legacy data should be profiled before conversion. The following checks expose empty tokens, non-numeric text, and leading or trailing separators rather than silently coercing malformed values to zero:

SELECT id, ref_csv
FROM legacy_relation
WHERE ref_csv REGEXP '(^,|,$|,,)'
   OR ref_csv REGEXP '(^|,)[[:space:]]*[^0-9][^,]*($|,)';

After invalid rows have been corrected, a recursive CTE can split a bounded legacy list. Run the migration in a transaction and compare counts before dropping the old column.

START TRANSACTION;

INSERT INTO article_reference (article_id, reference_id)
WITH RECURSIVE split AS (
  SELECT
    id AS article_id,
    TRIM(SUBSTRING_INDEX(ref_csv, ',', 1)) AS token,
    CASE
      WHEN INSTR(ref_csv, ',') = 0 THEN ''
      ELSE SUBSTRING(ref_csv, INSTR(ref_csv, ',') + 1)
    END AS rest,
    1 AS depth
  FROM legacy_relation
  WHERE ref_csv <> ''

  UNION ALL

  SELECT
    article_id,
    TRIM(SUBSTRING_INDEX(rest, ',', 1)),
    CASE
      WHEN INSTR(rest, ',') = 0 THEN ''
      ELSE SUBSTRING(rest, INSTR(rest, ',') + 1)
    END,
    depth + 1
  FROM split
  WHERE rest <> ''
    AND depth < 1000
)
SELECT DISTINCT
  article_id,
  CAST(token AS UNSIGNED)
FROM split
WHERE token REGEXP '^[0-9]+$';

COMMIT;

The depth guard makes an unexpectedly large or malformed row fail predictably rather than recursing without a business limit. The primary key remains the final duplicate defense. Foreign keys deliberately reject IDs that have no corresponding entity; inspect those failures instead of disabling integrity checks.

Validate at least the number of distinct source pairs, orphan count, duplicates, and a sample of relationships in both directions. Keep the CSV column read-only during verification, switch application reads to the junction table, and remove the old column only after the results agree.

When a JSON Array Is Acceptable

MySQL's native JSON type is preferable to CSV when a row genuinely owns an immutable document or historical snapshot. For example, an archived export may preserve the reference IDs that were visible at the time it was created:

CREATE TABLE article_snapshot (
  snapshot_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  article_id BIGINT UNSIGNED NOT NULL,
  reference_ids JSON NOT NULL,
  created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (snapshot_id),
  CHECK (JSON_TYPE(reference_ids) = 'ARRAY')
) ENGINE=InnoDB;

This is a document field, not the source of truth for a live many-to-many relationship. JSON preserves types and can be expanded with JSON_TABLE(), but it still cannot enforce a foreign key for every array element. If the application needs joins, reverse lookups, independent updates, relationship attributes, or referential integrity, use the junction table.

The Practical Rule

Normalize relationships that the application treats as relationships. Denormalize only a measured read model, cache, export, or immutable snapshot, while retaining an authoritative normalized source. One extra table is not accidental complexity here; it is the structure that makes membership explicit, enforceable, and indexable.