Relational, document or graph? Choosing the shape

A shop has customers, orders with line items, products, and customers who follow each other. Each data model stores that differently — and the difference is which questions are one step and which are many.

Relational: facts in tables, connected by keys

customers(id, name)   orders(id, customer_id, placed_at)   order_items(order_id, sku, qty, unit_price)

Each fact lives once; foreign keys and constraints keep the pieces consistent; any question — "revenue per product per month", "customers who bought X but not Y" — is a query, including ones nobody planned for. The price: showing one order means joining three tables.

Document: the aggregate stored whole

{ "_id": 1001, "customer": { "id": 7, "name": "Ada" }, "placedAt": "2026-03-10T09:15:00Z",
  "items": [ { "sku": "PEN", "qty": 2, "unitPrice": 1.5 }, { "sku": "PAD", "qty": 1, "unitPrice": 4 } ] }

The order and its items are one document: one read to show it, one atomic write to change it — in MongoDB an operation on a single document is atomic. MongoDB's modelling guide sums up the rule: data that's accessed together should be stored together. The price: questions that cut across documents ("revenue per product") have to dig into every order, and the customer's name copied into every order goes stale when it changes.

Graph: relationships as first-class data

(:Customer {name: "Ada"})-[:FOLLOWS]->(:Customer {name: "Grace"})-[:FOLLOWS]->(:Customer {name: "Linus"})

Neo4j stores nodes, relationships and properties; relationships are stored alongside the nodes rather than reconstructed by joins at query time. "Who is within three follows of Ada?" is a short pattern. The price: it's a specialised system for traversals, not a general-purpose store for everything else the shop needs.

The question that decides

Not "which is modern" or "which is fast" — but what will you read together, what must stay consistent across records, and how deep do your relationship questions go?

Most applications are mostly the first row, which is why a relational database is the usual default — and PostgreSQL's jsonb covers a surprising amount of the second row inside it.

A document database doesn't remove modelling; it moves the central decision. Instead of "which table?", it's "inside this document, or in its own document with a reference?" The MongoDB 9.0 data-modelling docs give the criteria; here they are applied to a shop.

Embed when the data lives and dies with its parent

{
  "_id": 1001,
  "customerId": 7,
  "shipTo": { "street": "1 Analytical Way", "city": "London" },
  "items": [
    { "sku": "PEN", "name": "Gel pen", "qty": 2, "unitPrice": 1.5 },
    { "sku": "PAD", "name": "A5 pad",  "qty": 1, "unitPrice": 4 }
  ]
}

• "Contains" and one-to-many-in-context relationships. Line items only make sense inside their order, and the shipping address is the address for this order.
• Read together. Showing an order is one read.
• Changed together, atomically. Adding a line item and changing the total is one single-document write, and MongoDB guarantees a single-document operation is atomic.

name and unitPrice are copied from the product at purchase time — snapshots, not stale copies (the normalization unit's distinction). That's the embedding sweet spot.

Reference when the data has a life of its own

{ "_id": 7, "name": "Ada", "email": "ada@x.io" }            // customers
{ "_id": "r-88", "productSku": "PEN", "stars": 5, … }       // reviews

The MongoDB docs list the cases for references:

• The embedded copy would change often — the customer's current email copied into every order would need updating in every order. Store it once and keep customerId.
• Many-to-many relationships or large hierarchies.
• The related data is queried on its own — reviews listed by author, or the latest across the whole site. (The modelling overview has a related example: a product page shows the five most recent reviews, while all reviews live in a separate collection because older ones are rarely accessed.)

Resolving a reference costs a second query or a $lookup aggregation stage — a join, by another name.

The hard limits

• Size. Documents must be smaller than 16 MiB. An array that grows without bound — every comment ever posted, every event for a device — eventually hits it. Unbounded children belong in their own collection.
• Transactions. MongoDB supports multi-document transactions (replica sets since 4.0, sharded clusters since 4.2), but its docs say they cost more than single-document writes and shouldn't replace good schema design. If you need transactions across documents for every write, the aggregate boundaries are probably wrong — or the data is relational.

The rule of thumb: embed what you read and write as a unit; reference what's shared, unbounded, or changes on its own schedule.

Relationship questions of known, shallow depth are joins. Questions of unknown depth — "everyone in Ada's reporting line", "everything that depends on this package" — need recursion. PostgreSQL has it.

A tree: the org chart

-- employees(id, name, manager_id REFERENCES employees(id))
WITH RECURSIVE reports AS (
  SELECT id, name, 1 AS depth FROM employees WHERE manager_id = 1     -- start: Ada's direct reports
  UNION ALL
  SELECT e.id, e.name, r.depth + 1
  FROM employees e JOIN reports r ON e.manager_id = r.id              -- step: their reports
)
SELECT name, depth FROM reports;
-- Grace 1, Linus 1, Margaret 2, Ken 3

The non-recursive term seeds a working table; the recursive term is re-run against the rows found in the previous round until a round finds nothing. With an index on manager_id, each round can be a handful of index lookups.

A graph with cycles: who can Ada reach?

-- follows(follower, followee): 1→2, 2→3, 3→1, 3→4, 4→5
WITH RECURSIVE reach AS (
  SELECT followee AS person, 1 AS hops FROM follows WHERE follower = 1
  UNION ALL
  SELECT f.followee, r.hops + 1
  FROM follows f JOIN reach r ON f.follower = r.person
  WHERE r.hops < 3                                                     -- bound the depth
) CYCLE person SET is_cycle USING path                                 -- PostgreSQL 14+
SELECT DISTINCT person FROM reach WHERE person <> 1;                   -- 2, 3, 4

Real social graphs have cycles (1 → 2 → 3 → 1), and without a guard the recursion never ends. The CYCLE clause (added in PostgreSQL 14) tracks the path and stops a branch that revisits a node; a depth bound does the rest. The path starts at Ada's first follow, not at Ada, so the walk back to her (3 → 1) isn't flagged as a cycle — that's what person <> 1 filters out.

Where recursion in SQL stops being enough

Each round of recursion multiplies the rows by the average number of edges: with 200 follows per person, that's 200 rows at one hop, up to 40,000 at two and 8 million at three (paths, before DISTINCT collapses them). That fan-out is a property of the graph, not of SQL — any system that enumerates those paths faces it. What SQL adds is the cost per hop: each one is an index lookup and a join. The CTE is fine for trees (one parent each), dependency chains and bounded neighbourhoods. It gets painful when the product is traversal: recommendations from friends-of-friends, fraud rings linked through shared devices and cards, shortest paths through a network.

That's what graph databases are built for. Neo4j stores relationships natively alongside nodes, so following one is a step along stored data rather than a join — each hop is cheaper, though the number of paths grows just the same. Cypher (as documented in the current Cypher manual) writes the traversal as a pattern:

MATCH (ada:Person {name: 'Ada'})-[:FOLLOWS]->{1,3}(p:Person)
WHERE p <> ada
RETURN DISTINCT p.name

{1,3} is a quantified relationship — one to three hops — and Cypher has path-finding built in (ALL SHORTEST finds all shortest paths by hop count).

The cost of adding one

A graph database is a second system: a second copy of the data to keep in sync with your main database, no shared transaction across the two, another thing to operate and back up. Reach for it when traversals of unknown depth are central and measured to hurt in SQL — not merely because the data has relationships. Foreign keys and joins handle relationships of known depth well.