Part 3 of 6 · 2 min read
One-to-many fits naturally; many-to-many does not
A document's nested tree matches a one-to-many relationship directly, but has no natural way to express a shared reference.
Think of a single folder holding one order. Clipped inside are copies of its own line items. Now imagine trying to also clip a shared customer loyalty card into every order that customer has ever placed. The card cannot physically sit inside more than one folder at once.
In plain words: a one-to-many relationship nests naturally inside a single document. One order has many line items. The many side never needs to be shared with, or referenced from, any other document. A many-to-many relationship breaks this pattern. Say a book has many authors, and an author has written many books. A document holding each book's authors has two options. It can duplicate each author's details inside every one of their books' documents. Or it can store a reference and perform a separate lookup. That second option is exactly the join a document database was meant to avoid. The more a bookstore's data grows interconnected, authors linked to books, books linked to customer reviews, customers linked to other orders, the less a pure document model helps. It starts needing the same joins a relational schema already provides directly.
A worked example. A cookbook is co-written by two authors. The book's document can duplicate each author's full biography inside itself. That risks an inconsistent, half-updated biography if an author's details change later. Or the book document can store only an author ID. That requires a second lookup against an authors collection, an application-level join in all but name.
The key lines. No new SQL; this part contrasts a nested array, which a document model handles directly, against a shared reference, which it cannot embed without duplication.
Many-to-many relationship (in a document model)
A many-to-many relationship exists when each record on one side can relate to several records on the other side, and vice versa, such as books and authors. A document model has no native way to embed such a relationship. It must either duplicate the related data in every document that needs it, or store a reference and perform a separate lookup to resolve it.
Example: a book co-written by two authors cannot cleanly embed each author's full, independently-updatable record inside its own document; a many-to-many relationship like this is exactly where the document model's one-to-many strength stops helping.