Databases · Lesson 6.1
When data doesn't fit columns
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.
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.
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.
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.
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.
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.
Which of these is data whose shape varies from one record to the next?
Databases · Lesson 6.2
Storing a document in a column
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.
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.
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?
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.
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.
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?
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?
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?
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?
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?
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?