Understanding SQL ↔ NoSQL schemas
Normalise or embed — the design decision that follows the queries.
When to embed, when to reference, the access-pattern rule that decides, and the document-key conventions that don't require a join.
The opposite default.
SQL's default is normalisation: each piece of data in one place, joins to reassemble. NoSQL's default is denormalisation: copy data into every document that needs it, so reads are single-document. SQL optimises for write integrity at the cost of read complexity. NoSQL optimises for read simplicity at the cost of duplication and the discipline to keep copies in sync.
The access-pattern rule.
NoSQL schema design starts with "what queries does this app run?", not "what entities exist?". An access pattern that reads a user and their three most recent orders together every time is best served by embedding orders in the user document — even though SQL would never do that. An access pattern that reads orders without user context (analytics, reporting) wants a separate orders collection. List the access patterns first; the schema falls out.
Embed vs reference.
Embed when: the embedded items are bounded (a user has 0-10 addresses, not unbounded), they're always read with the parent, they don't need to be queried independently. Reference when: the items are unbounded (a user's order history, a post's comments), queried alone, or updated frequently in ways that would touch many parent documents. The hybrid — embed recent items + reference the full set — is common for activity-feed shapes.
A worked schema.
E-commerce app, DynamoDB. Access patterns: get user with their open orders; get an order with its line items; list user's order history. Schema: USER#u123 / PROFILE — user profile. USER#u123 / ORDER#o7 — order header with line items embedded as a list (bounded, always read together). USER#u123 / ORDER_LIST — a denormalised index of order IDs and totals for listing. Three patterns, one partition key. The same data in PostgreSQL would be three tables and two joins.
E-commerce in DynamoDB
single-table design
Partition by user, sort by item type.
USER#u123 + PROFILE/ORDER#o7/ORDER_LIST
= One query per pattern
Migrating SQL → NoSQL.
The wrong way: map each table to a collection one-for-one. You get a normalised schema in a database that doesn't join — every read fans out into multiple queries the application has to stitch together. The right way: re-derive the schema from access patterns, even if that means duplicating data and writing application-level consistency logic for the few writes that touch multiple documents. The migration is more architecture than data movement.
When to switch back.
The team picked NoSQL early; the access patterns have grown beyond what they were when the schema was designed; new queries need joins that don't fit. The fix is rarely to add joins in application code — that's slow and wrong. The fix is to add a query path (a secondary index, a denormalised projection) or, if the patterns are truly OLAP-shaped, to dual-write to a SQL warehouse and run analytics there. Both stores, each for what it's good at.