NoSQL Modeling: Denormalization & Single-Table Design

Take the marketplace's order screen — order details, the dishes in it, the restaurant's name — and point it at DynamoDB. Now try to build it the way the last lesson taught: normalized tables, an index per hot pattern, a join at read time.

You can't. There is no join. There's no CREATE INDEX on whatever column you like, either. The store you met as the coat check and the cabinet answers exactly one question shape natively — items by key, sorted within a partition — and everything else simply isn't on the menu. Five entities on one screen means five round trips… unless the data was arranged so it doesn't.

That's the entire discipline of this lesson, and it fits in one sentence — the sentence Rick Houlihan, who built the pattern at Amazon, still stands behind after a decade of debate: what is accessed together should be stored together. If the read can't assemble the aggregate, the write must. The join doesn't disappear in NoSQL; it moves to write time — and once you see that, denormalization stops being a sin you commit and becomes a contract you sign, with terms you can read in advance.

This is the method lesson's sequel, run where the physics are least forgiving: same marketplace, same pattern table, same six steps — but no joins, no ad-hoc indexes, and duplication as a first-class tool. By the end you'll model the same app both ways and see precisely what each shape buys and bills.

The single mechanism underneath every single-table trick: in a partitioned store, items that share a partition key live together, sorted by their sort key. That group — one PK, many SKs — is an item collection, and it is the aggregate, pre-joined:

PK          SK                        (what it is)
REST#7      META                      the restaurant itself
REST#7      DISH#99                   a menu item
REST#7      DISH#12                   another one
USER#8412   META                      the customer
USER#8412   ORDER#2026-07-13T09#501   an order — sorted by time
USER#8412   ORDER#2026-07-12T20#488   the one before it

Query(PK = REST#7) returns the restaurant and its whole menu in one request — the six-way join from the hook, performed once at write time and served forever. Query(PK = USER#8412, SK begins_with ORDER#, descending, limit 20) is "my latest 20 orders" as one sorted range read — the clustering-key trick from the cabinet lesson, doing its third tour of duty.

Three habits make this workable at scale. Generic key names — the columns are literally called PK and SK, holding typed prefixes (REST#7, ORDER#<timestamp>#501), because different entity types share the table. Sortable ids — timestamps or ULIDs in the sort key make time ranges free. And the design artifact that replaces the ER diagram: the entity chart — one row per entity type listing its PK template, SK template, and which access pattern it exists to serve. It's the access-pattern table from the method lesson, made physical.

And notice what the partition key actually is: you drew this boundary already. The aggregate boundary from the method lesson — the entities that change and load together — is precisely what becomes a shared PK. Modeling didn't change; the boundary just stopped being an annotation and became an address.

The order screen still needs the restaurant's name, and the order lives in USER#8412's collection while the name lives in REST#7's. One screen, two partitions, two requests — unless you duplicate. There are exactly two moves, and each comes with terms.

Move one: copy an attribute. Write restaurantName into every order item at creation. The order screen drops to one request. The terms: the marketplace has copies now — when a restaurant renames itself, someone must update every order item carrying the old name, or accept the drift. You are the consistency mechanism. (In practice: a stream/async fan-out, or a decision that historical orders showing the old name is fine — often it genuinely is.)

Move two: copy an item. Write the whole order twice — once under USER#8412, once under REST#7 — and both "my orders" and "the restaurant's orders" become one-request patterns. The terms: double the writes, forever, and both copies to fix on every change.

Signing rules, learned from production scars:

• Immutable data duplicates free. The order's priceAtPurchase isn't just allowed to be a copy — it must be one. A receipt snapshots; it doesn't reference. If dish prices change tomorrow, last week's order must not. (Sometimes "denormalization" is just correctness wearing a scary name.)
• Bounded fan-out only. Copying a name into a user's ~dozens of orders is a contract you can honor. Copying it into something that grows without limit re-opens the unbounded-growth wound — count N before signing.
• Mutable + hot = think twice. A field that changes often, copied widely, is a standing fan-out storm.

That's "duplicate deliberately": not whether it's allowed — it's the tool — but reading the update-cost contract out loud before signing it. The studio below makes you feel the signature.

Run the method lesson's pattern table through both designs and put them side by side:

pattern	SQL (last lesson)	single table (this one)
menu of a restaurant	join + idx(rest_id) — 1 read	Query(REST#7) — 1 request
order screen w/ name	join at read — 1 read	copied name — 1 request
my latest 20 orders	idx(user, created ↓) — 1 read	Query(USER#…, ↓, 20) — 1 request
restaurant's today	idx(rest, created) — 1 read	GSI (next section) — 1 request

Both columns land at one per pattern. So what did the exercise buy? Look at where the work happens and who pays for change:

SQL re-decides the join at every read. The planner assembles the answer fresh each time — which is exactly why a brand-new question costs one CREATE INDEX and nothing else. Flexibility is the product.

The single table pre-decides the join at every write. The answer is assembled once, stored, and served — which is why reads are flat, cheap and planner-free at any scale, and why a brand-new question is a renovation. Efficiency is the product.

Same requests today; different bills tomorrow. That's the honest comparison — and it's why the rubric, not fashion, decides which side of this table you live on. (It's also why the checkout itself stayed on Postgres in our rubric run: money invariants plus unknown queries pointed left. The pattern-locked, scale-brutal workloads point right.)

So the PM walks in — it's tradition by now — and asks for "a restaurant's orders, today." Your orders live under USER# partitions. The single table has no answer… and this is where the escape hatch earns its keep.

A global secondary index is your table, re-keyed: pick a new PK/SK (GSI1PK = REST#7, GSI1SK = timestamp), choose which attributes to project, and the store maintains that re-shaped copy for you. The new question becomes one Query against the index. Sound familiar? It's this world's CREATE INDEX — with two differences you must price:

Every write to an indexed item now writes ~twice — base table plus GSI replication. The write-tax law from the method lesson, at full volume. And GSI reads are eventually consistent — the copy lags the table by design, so check the pattern's staleness budget (you have one; the rubric asked).

Two refinements worth knowing. Sparse indexes: only items having the GSI key appear — write the attribute onto just open orders, and the index IS the "open orders" view, tiny and cheap. And index overloading — many patterns routed through one GSI with typed keys — which deserves its history told straight: it was invented when DynamoDB capped you at five GSIs, each with its own provisioned capacity and no on-demand billing. Those limits are gone (twenty-five GSIs, on-demand, adaptive capacity), and when Houlihan was asked in 2024 whether he'd still teach the old contortions: "Of course not." The tricks were workarounds; the workarounds expired; the principle — accessed together, stored together — didn't.

Single-table design went through a full fashion cycle — doctrine (~2018), backlash (~2022), synthesis — and the synthesis is worth stating plainly, because both extremes still roam the internet.

Where it's the wrong tool, per its own advocates. Alex DeBrie's line is the fair one: skip it "whenever you need query flexibility and/or easier analytics more than blazing-fast performance." Single-table optimizes runtime over developer — for a modest-scale CRUD app, a boring multi-table (or Postgres) design is usually cheaper in total cost once engineering hours are on the bill.

Analytics pays a second toll. The pre-joined, type-mixed table is hostile to OLAP — production teams stream or export it back OUT, un-winding the denormalization into normalized, columnar form downstream. Recognize the shape: that's the composition pattern from the OLTP-vs-OLAP lesson, with an extra step because the OLTP side pre-shaped so aggressively. The dashboard was never going to run on this table; budget the pipeline from day one.

Boundaries, even here. Houlihan's own 2024 don'ts: don't force unrelated data into one table, and don't stretch one table across service boundaries. "Single table" was always "one table per related-access domain" — never "one table for the company."

And where it's unbeatable: pattern-locked, invariant-light, scale-brutal workloads — the coat-check and cabinet territory where this course has already shown you Amazon's own tier-0 running at Prime-Day millions-per-second. When the bet is real, nothing else touches it.

That's the bill. The doctrine's one durable sentence survived every swing of the cycle, and it's the sentence you take into the interview: what is accessed together should be stored together — then show the entity chart, and read the duplication contracts out loud.

Step back — because this lesson closes the section that opened with one overloaded database and a map.

You now hold the whole arc: eight families, each a layout bet on one native question; the axis underneath them all (rows for transactions, columns for analytics); a rubric that turns the fake SQL-vs-NoSQL binary into six answerable questions; a method that models the world and prices the queries; and — as of now — the discipline for the far shore, where joins move to write time and duplication is signed, not sinned.

One thread ran through all of it, and it's the one to keep: work is proportional to the question, and every layout — table, index, partition, collection — is a bet on which question matters. Engines differ; the law doesn't.

Next comes the section checkpoint: five real workloads, and you pick the family — with numbers, with the rubric, out loud. After that, the course drops below the API line: §2 opens the engine itself — how a B-tree actually finds your row, why LSM-trees eat writes for breakfast, and what the WAL is really promising you. You've been trusting the machinery for thirteen lessons. Time to open it.