The Lattice: Recursive Many-to-Many Relationships

Opening Scene

A woven lattice panel is built from strips that are all genuinely the same kind of thing — every strip is a lattice strip, no different category from any other — yet each one crosses over and under several other strips of that identical kind, at multiple points, with no strip playing a “parent” role to any other. Compare that to the staircase from Article 3, where each step relates to exactly one step below it in a strict single chain. A lattice is looser and richer: any strip can cross any number of others, and the relationship is fundamentally symmetric, not hierarchical.

That’s the shape of a recursive many-to-many relationship — a table related to itself, but where each row can relate to many other rows of that same type, not just one parent.

In Plain English

A recursive many-to-many relationship combines two patterns already covered in this series: the self-reference from Article 3, and the many-to-many junction entity from Article 2, applied to a single table relating to itself. A Product table where products can each be “related to” several other products — a common bundling or “customers also bought” relationship — is a classic example. A Person table where people can each be “friends with” or “connected to” several other people is another. Because any product or person can relate to many others of the same type, and those others relate back to many more, a junction table referencing the same parent table twice — once for each side of the pairing — is required to hold it.

The Old Way

Recursive many-to-many relationships have always been one of the trickier patterns to get right in practice:

Some designs mistakenly tried to model this with the simple single self-referencing foreign key from Article 3, which only supports a strict one-parent hierarchy, not a genuinely symmetric web of relationships where any row can connect to any number of others.
Others avoided the pattern entirely, flattening it into a fixed set of “related item 1, related item 2” columns, capping how many connections a row could genuinely have, the same mistake Article 2 warned against for ordinary many-to-many relationships.
The correct approach uses a junction table with two foreign keys, both pointing back to the same parent table, exactly mirroring the standard many-to-many junction pattern but recognizing both sides come from the same entity type.

A genuinely important design decision within this pattern is whether the relationship is symmetric (if A relates to B, then B automatically relates to A) or directional (A recommending B doesn’t necessarily mean B recommends A) — getting this distinction wrong quietly corrupts every query built on top of it.

What’s Changing (and Why AI Is the Reason)

AI-powered recommendation systems generate exactly this kind of recursive many-to-many data at genuine scale — “customers who bought this also bought that” relationships between products, or “people you may know” connections between users — making this pattern’s correct implementation a direct, practical prerequisite for common AI-driven features, not just an academic modeling exercise.
AI-assisted schema tools can recognize when a self-referencing structure needs the many-to-many treatment rather than the simple hierarchical treatment from Article 3, checking whether real relationships in the data genuinely branch symmetrically rather than forming a strict single-parent tree, and flagging a mismatch between the two patterns.
AI agents traversing recursive many-to-many structures for tasks like graph-style reasoning or similarity search need the symmetric-versus-directional distinction to be explicit and correctly enforced, since an agent computing “who’s connected to whom” will silently produce wrong answers if a directional relationship was mistakenly treated as symmetric, or vice versa.

The Metaphor, Fully Extended

Joinery Element	ER Modelling Concept
A single lattice strip, structurally identical to every other strip in the panel	A row in a table that relates to other rows of that same type
One strip crossing several others at multiple points	A row connecting to many other rows of the same entity, not just one
The crossing point itself, where two specific strips interweave	A row in the junction table, holding two foreign keys back to the same parent table
A lattice pattern where every crossing works the same in both directions	A symmetric recursive relationship, where A relating to B implies B relates to A
A one-way trellis guide, where a vine is directed along one strip but not the reverse	A directional recursive relationship, where A relating to B does not imply the reverse

For Beginners: What to Actually Do

Recognize a recursive many-to-many relationship by its shape: a table relating to itself, where any row can relate to many other rows of the same type, not just one parent.
Model it with a junction table holding two foreign keys, both referencing the same parent table’s primary key.
Explicitly decide and document whether the relationship is symmetric or directional before writing any query against it, since this fundamentally changes how the data should be interpreted.
Practice distinguishing this pattern from the simple single-parent hierarchy in Article 3 — the presence of a junction table versus a single foreign key column is the clearest tell.

For Practitioners and Leaders: The Deeper Layer

Treat “related items,” “similar customers,” and “connections between people” features as recursive many-to-many patterns from the start, since these are exactly the shapes AI-driven recommendation and social features tend to produce at scale.
Require the symmetric-versus-directional decision to be documented explicitly for every recursive many-to-many relationship in your schema, since it’s easy to get silently wrong and expensive to discover late.
Use AI-assisted schema review to catch cases where a genuinely recursive many-to-many structure was mistakenly modeled as a simple single-parent hierarchy instead.
Recognize this pattern as a direct prerequisite for common AI features like recommendations and social graphs, making its correct implementation a practical priority, not a theoretical one.

Quick Recap

A recursive many-to-many relationship combines self-reference with many-to-many, letting any row connect to many other rows of the same type, the same way any lattice strip crosses several others of its exact same kind.
It requires a junction table with two foreign keys, both pointing back to the same parent table.
Deciding whether the relationship is symmetric or directional is a critical, easy-to-miss judgment call.
AI-driven recommendation and social features generate this exact pattern at scale, making its correct implementation a genuine practical prerequisite rather than an academic exercise.

Where This Fits in the Series

This article closes out the series’ intermediate patterns, combining ideas from Articles 2 and 3 into one richer structure. Article 10 opens the next stretch of the series — applying these patterns well — starting with recognizing which pattern genuinely fits a real business shape.