Simplified
Simplified

L6.1When data doesn't fit columns

YOU

When data doesn't fit columns

usersid123emailada@simplified.orgben@simplified.orgcy@simplified.orgnameAdaBenCyxp120400relational
beach postYyou···1/3you Beach dayMalibucoffee postYyou···you Coffee?non-relational
productsid123nameT-shirtMacBook AirAtomic Habitsprice15.00999.0014.99sizeMNULLNULLcolorblueNULLNULLramNULL16 GBNULLscreenNULL13.6 inNULLbatteryNULL18 hNULLauthorNULLNULLJames ClearpagesNULLNULL320?NULLNULLNULL?NULLNULLNULL?NULLNULLNULLmigration

Databases · Lesson 6.1

Documents in a Table

When data doesn't fit columns

Simplified
0:00 / 2:00

In Simplified's users table, every learner has an id, an email, a name and their XP.

Every row has the same columns: that's a relational database, data in rows and columns.

But some data's shape varies from one record to the next, like Instagram posts.

One post has a caption, 3 photos and a location.

Another has just a caption and 1 photo.

A database that keeps data like this as documents, not rows and columns, is non-relational.

beach postYyou···1/3you Beach dayMalibucoffee postYyou···you Coffee?

So what happens when data whose shape varies has to fit one table?

Say Amazon keeps all its products in one table, called products.

Every product has a name and a price, so each gets a column, beside an id.

A T-shirt also has a size and a color.

So the table gets two more columns: size and color.

productsidnamepricesizecolor1T-shirt15.00Mblue

Then Amazon adds a laptop, a MacBook Air.

A laptop has RAM, a screen size and a battery life.

So the same table grows three more columns: ram, screen and battery.

A laptop has no size or color, so those two cells hold NULL.

NULL means a cell holds no value at all, like a name a learner skipped.

And the T-shirt has no RAM, screen or battery, so its three new cells are NULL too.

productsidnamepricesizecolorramscreenbattery1T-shirt15.00MblueNULLNULLNULL2MacBook Air999.00NULLNULL16 GB13.6 in18 h

Then a book, Atomic Habits, which has an author and a page count.

That's two more columns, author and pages, NULL in the other two rows.

So the table now has 7 columns for things only some products have.

The T-shirt fills just 2 of them, its size and color.

Its other 5 are NULL: the laptop's three, and the book's two.

productsidnamepricesizecolorramscreenbatteryauthorpages1T-shirt15.00MblueNULLNULLNULLNULLNULL2MacBook Air999.00NULLNULL16 GB13.6 in18 hNULLNULL3Atomic Habits14.99NULLNULLNULLNULLNULLJames Clear320

And every new kind of product means another column, NULL in every other row.

And each new column means writing another migration.

Amazon sells many kinds of product, so the columns would keep piling up.

When data's shape varies, one table needs a column for every possible thing, mostly NULL in each row.

Example: When data doesn't fit columns

Say Amazon keeps its products in one table, with a column for every thing only some products have: a T-shirt's size and color, a laptop's RAM, screen size and battery life, and a book's author and page count.

How many of those columns are NULL in the laptop's row?

Explanation

First, there are 2 + 3 + 2 = 7 of those columns.

Then, the laptop fills only its own 3: RAM, screen size and battery life.

Therefore, 7 − 3 = 4 of them are NULL in the laptop's row.

Question 1

Which of these is data whose shape varies from one record to the next?

Storing a document in a column

productsid1234nameT-shirtMacBook AirAtomic HabitsCoffee makerprice15.00999.0014.9949.99sizeMNULLNULLcolorblueNULLNULLramNULL16 GBNULLscreenNULL13.6 inNULLbatteryNULL18 hNULLauthorNULLNULLJames ClearpagesNULLNULL320attributesjsonb{"size": "M", "color": "blue"}{"ram": "16 GB", "screen": "13.6 in", "battery": "18 h"}{"author": "James Clear", "pages": 320}{"cups": 12}?NULLNULLNULL?
fetch()response.json()documentT-shirt"size": "M""color": "blue"MacBook Air"ram": "16 GB""screen": "13.6 in""battery": "18 h"Atomic Habits"author": "James Clear""pages": 320
productsid1nameT-shirtprice15.00sizeMcolorblueramNULLscreenNULLbatteryNULLauthorNULLpagesNULLattributesjsonb{"size": "M", "color": "blue"}?NULLPostgres
productsid1234nameT-shirtMacBook AirAtomic HabitsCoffee makerprice15.00999.0014.9949.99sizeMNULLNULLcolorblueNULLNULLramNULL16 GBNULLscreenNULL13.6 inNULLbatteryNULL18 hNULLauthorNULLNULLJames ClearpagesNULLNULL320attributesjsonb{"size": "M", "color": "blue"}{"ram": "16 GB", "screen": "13.6 in", "battery": "18 h"}{"author": "James Clear", "pages": 320}{"cups": 12}?NULLNULLNULLmigrationrelational?

Databases · Lesson 6.2

Documents in a Table

Storing a document in a column

Simplified
0:00 / 1:38

In Amazon's products table, a T-shirt's row left 5 cells NULL: the laptop's three and the book's two.

But how can each product keep just its own things, with no NULLs?

Keep the part that varies as a document, not as a column for every possible thing.

When your frontend code calls fetch(), response.json() gives you a JSON object.

That object holds only what it has, like a T-shirt's size, M, and its color, blue.

So it's a document, like each Instagram post: one record kept together, holding only what it has.

T-shirt"size": "M""color": "blue"MacBook Air"ram": "16 GB""screen": "13.6 in""battery": "18 h"Atomic Habits"author": "James Clear""pages": 320

So the MacBook Air's document holds just its RAM, screen size and battery life.

And Atomic Habits' holds just its author and page count.

And Postgres can keep a document like this in one column of an ordinary row.

That column's type is called jsonb.

productsidnamepriceattributesjsonb1T-shirt15.00{"size": "M", "color": "blue"}2MacBook Air999.00{"ram": "16 GB", "screen": "13.6 in", "battery": "18 h"}3Atomic Habits14.99{"author": "James Clear", "pages": 320}

So Amazon swaps its 7 columns for one column, called attributes, holding each product's document.

That leaves 4 columns: id, name, price and attributes, and no NULLs.

Every product has a name and a price, so those stay ordinary columns.

So the table is still relational: every row has the same 4 columns.

And what happens when Amazon starts selling coffee makers?

productsidnamepriceattributesjsonb1T-shirt15.00{"size": "M", "color": "blue"}2MacBook Air999.00{"ram": "16 GB", "screen": "13.6 in", "battery": "18 h"}3Atomic Habits14.99{"author": "James Clear", "pages": 320}4Coffee maker49.99{"cups": 12}

In the old table, that meant another column, NULL in every other row.

Now a coffee maker just gets a row, with its own document, like making 12 cups.

So the table still has 4 columns: no new column, and no migration.

When a record's shape varies, keep the varying part as a document: Postgres can hold it in one column.

Learn more: Postgres's guide to JSON types, on how Postgres keeps documents, and when a column still wins.

Example: Storing a document in a column

Say Etsy keeps its listings in Postgres. Every listing has a title and a price. A mug also has a capacity, a ring has a ring size and a metal, and a poster has a width and a height.

Which part should Etsy keep as a document?

Explanation

First, every listing has a title and a price, so those fit ordinary columns, never NULL.

Then, a capacity, a ring size, a metal, a width and a height each belong to only some listings.

Therefore, the things only some listings have go in each listing's document.

Question 1

Say Todoist keeps tasks in Postgres, and every task has the same three things: a title, a due date, and whether it's done.

Should it keep each task as a document, to avoid NULLs?

Question 2

Say Amazon keeps each product's own things as a document, in one column of its products table. A T-shirt's document holds its size, M, and its color, blue. Laptops in the same table have RAM.

What does the T-shirt's document hold for RAM?

Question 3

Say Amazon keeps its products in Postgres, in a table with 4 columns: id, name, price, and attributes, which holds each product's own things as a document.

Is the products table still relational?

Question 4

Say Best Buy's products table has an id, a name and a price, plus 12 more columns for things only some products have. Best Buy moves those 12 things into one column that holds each product's own things as a document.

How many columns does the table have now?

columns

Question 5

Say Amazon's products table has 4 columns: id, name, price, and attributes, which holds each product's own things as a document. Amazon starts selling headphones, which have one thing no product had before: whether they cancel noise. It also starts selling tents, which have two new things: how many people they sleep, and their weight.

How many columns does the table have once both are added?

columns

Question 6

Say Zillow keeps its homes in Postgres. Every home has an address and a price, but the rest varies: a house has a garden size, an apartment has a floor number and an elevator, and a cabin has a fireplace.

Which of these keeps each home's own things as a document?

Assistant

Highlight lesson text and click Ask AI, or type a question below.