The Nesting Decision

A nested column holds a structure where a flat column holds a single value. An order row in a flat schema stores customer_id, order_date and total, 1 value per column. The same order in a nested schema can also carry items, a list of 3 records, each with a sku, a quantity and a price. The list sits inside the row, so reading the order reads its items without a join.

Order 1001 occupies 1 row in orders and 3 rows in order_items, linked by order_id. Splitting a repeating part into its own table like this, 1 row per item, is called normalizing. The embedded version keeps the same facts in 1 table:

Both versions answer every question about these orders. They differ in what a row means and therefore in which queries are cheap. In the normalized version, a row of order_items means 1 item of 1 order, so counting how many times TEE-M sold is a filter and a sum over 1 table. In the embedded version, a row means 1 complete order, so loading an order for the checkout page is 1 row read, and counting TEE-M sales means opening every list.

Five questions that decide it

• Ownership: A line item without its order means nothing, which argues for embedding. A product referenced by 10,000 orders has a life of its own, so it stays in a table.
• Read pattern: Checkout loads an order with its items every time. A nightly report reads items alone, millions of them, and wants them as rows.
• Update pattern: Amenities change when a host edits the listing, as a set. A review is edited by its author with no change to the listing, and belongs in its own table.
• Size bound: Amenities come from a fixed list of 120 options, so a listing holds at most 120. Reviews on a popular listing pass 2,000 and keep growing, and a row that grows forever is a row you cannot embed.
• Sharing: A host with 5 listings is 1 host. Embedding the host in each listing makes 5 copies, and copies drift apart the first time the host changes their phone number.

Run the Airbnb listing through those 5 questions. Amenities exist only for the listing, load with it, change with it, stay small and belong to 1 listing: embed. Reviews are written and edited on their own, grow without a ceiling and are read in bulk by the ratings pipeline: normalize. The host is shared across listings, so the listing stores a host_id and the host table holds the facts, with at most a display name copied in for the search card.

Embed when

• The child has no meaning outside this parent
• Parent and child are read together on the hot path
• The list has a small, known ceiling
• The child changes only when the parent changes

Normalize when

• The child is shared by many parents
• The child is queried in bulk across parents
• The list grows without bound
• The child is edited by a different process or team

A food delivery app models restaurants. Each restaurant has opening hours, menus, reviews and orders. Which of these belongs embedded in the restaurant row?

• The orders placed at the restaurant, since each order belongs to exactly 1 restaurant
• The reviews, since they are shown on the restaurant page next to the menu
• The opening hours, 7 entries with an open and close time for each weekday
• The delivery drivers currently assigned, since dispatch reads them with the restaurant

About this lesson

This section is part of the Nested Data lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.