Embed vs reference: should one order be one document?

For an order, embed vs reference follows one rule: embed what you read together, reference what must stay current, and copy values that belong to the moment of purchase, like the price paid. In our test on 2026-11-13, one order stored as a document read in 1 query, while renaming one popular product rewrote 1,178 order documents and changed 1 row in normalized tables.

What does embed vs reference mean for an order?

Embedding means storing related data inside its parent record. An order document holds the customer, the shipping address and every line item, with each product's name copied in. Referencing means storing an ID that points to a record kept somewhere else, so the order line holds product_id and the name lives once, in products.

Our relational version uses five tables and stores the checkout price on each order line. We compare reading them with five separate queries against one join.

MongoDB recommends embedding related data queried together and referencing data that grows without bounds (MongoDB, data modeling best practices). Line items belong to one order and stop growing once it is placed, so they are a natural fit. A product name is different, because thousands of orders share it.

We looked at the same trade-off per user, with a much bigger rename, in SQL vs NoSQL: choose the data shape first. Here the question is narrower: a single order and the fields inside it.

What we ran

The script is labs/sd-23/compare-order-document-vs-joined-tables.mjs. It builds the same shop data from a fixed seed (2311) in two in-memory SQLite databases with Node's built-in node:sqlite: 10,000 orders with 3 to 7 items each, 500 products and 2,000 customers. One database is normalized into five tables. The other keeps one JSON document per order, plus an index from product to orders, the way a document database can index items.productId.

Before timing, the script checks that all three read methods return exactly the same order for each of 1,000 random IDs. It then times 5 rounds and renames the most popular product 5 times. The numbers below come from one captured run on an Apple M2 Pro with Node v22.13.1.

How fast is reading one order?

Method	Queries per read	Median	p95
Document (fetch by key, then JSON.parse)	1	4.2 µs	6.9 µs
One join across five tables	1	18.2 µs	24.3 µs
Five separate queries	5	17.9 µs	21.8 µs

On one machine with no network, the document read took about a quarter of the join time, and both are tiny next to the cost of a round trip. The script models each query as a sequential request with an assumed 0.5 ms round trip, which is plain arithmetic, not a measurement: the document and the single join both cost about half a millisecond per order, and the five-query pattern costs 2.52 ms. Loading an order with one query per table is common, and in that case the network costs far more than the join does.

What does a product rename cost?

The most popular product, "Classic Charger 1", appears in 1,178 of the 10,000 orders. Say the shop renames it and wants old orders to show the new name.

Layout	What changes	Size
Normalized tables	1 row in products	one name
Order documents	1,178 documents read, changed and written back	719.1 KiB of JSON

The tables change one row because the name lives in one place. Every order document kept its own copy of the name, so every copy has to be rewritten. The 719.1 KiB is the JSON text the script hands to UPDATE, not the bytes SQLite writes to disk. MongoDB also warns that frequently updating duplicated data increases workload (MongoDB, data modeling best practices).

To find those 1,178 orders, the rename uses a helper table indexed on (product_id, order_id), so it gets the matching order IDs without scanning every document. The table models a MongoDB multikey index, which creates entries for distinct array values pointing to their documents, and it returns every order that contains the product ID. HNSW vector search also avoids a full scan, though its approximate graph traversal can miss neighbors. The script then rewrites each matching document inside one SQLite transaction, so all 1,178 change together or none do. MongoDB updates are atomic per document; an updateMany operation needs a transaction for all-document atomicity.

Atomicity does not stop lost updates. If two workers read a document and replace it after making different changes, the later replacement can overwrite the earlier change. For application-side replacement, include the expected version in the update filter, increment it on success, and reload and retry when no document matches. A transaction makes the grouped writes atomic; the update condition detects a stale version (MongoDB, atomicity and transactions).

Which order fields should you copy?

The rename only hurts if old orders must show the current name. Often they should not. Decide per field whether it is a snapshot of the moment of purchase or live shared data.

Field	Snapshot or live?	What to do
Line items, quantities	belong to this order	embed
Price paid	snapshot at checkout	copy into the order
Product name on the receipt	usually a snapshot	copy, and accept that old receipts keep the old name
Product name shown as current	live	reference by product_id, look it up with a join or a batched query
Customer's current email	live	reference; a stale copy here is a bug
Shipping address accepted for this order	snapshot	copy into the order or reference an immutable address version

The lab uses one shared address per customer and does not test address changes. Updating that address would change the normalized read; the embedded order would keep its original address.

A receipt should show what the customer paid on the day they paid it. If the price changes next week, the old order must not change with it, so the copied price is intended and needs no cleanup. A customer's email works the other way: when they update it, every place that sends them mail needs the new one.

Limits of this test

• One laptop, SQLite in memory for both layouts. A real document database and a real SQL server have different storage engines, caches and drivers.
• No network. The round-trip numbers are a model at 0.5 ms per query.
• JSON.parse is included in the document read; a database that returns binary documents would behave differently.
• Row counts are logical, computed from the result shape. The rename size is the JSON payload submitted, not physical writes.
• Timings drift run to run; the counts (1 query, 5 queries, 1 row, 1,178 documents) are fixed by the seed.

Our take

Store an order as one document when the app mostly reads whole orders, and keep the fields inside it that belong to that order or to the moment it was placed. Point to anything that must stay current everywhere. The stored order document still reads in one query, resolving live references may take additional queries or a server-side lookup, and renaming a referenced product changes one product record. When the same data is read far more than it changes, the reasoning is close to splitting reads and writes across databases: start from what the app does most.

The shop data, the orders and the product names are synthetic, made from a fixed seed. The schema is a typical design for teaching, not any company's system. Timings come from one run on one laptop and change from run to run.