databases/why-databases-split-your-data-into-two-tables-and-link-them-by-id.md

Why databases split your data into two tables (and link them by id)

Databases · 10 min read ·

A database table looks like a spreadsheet: columns for each kind of information, one row per thing. But it comes with rules. Every column has a data type, and every row gets a primary key, an id that is never empty and never repeated.

The real trick is splitting data. Keep customers and orders in one big sheet and the same email ends up copied everywhere, so one missed edit makes the table contradict itself. Store each fact once, link tables with a foreign key, and change an email in exactly one place.

$ ls databases/

see all →
databases/sql-window-functions-how-every-row-gets-a-rank-or-running-total-withou.md SQL window functions: how every row gets a rank or running total without collapsing the table A window function adds a calculated column to every row without collapsing rows the way GROUP BY does. Learn what OVER, PARTITION BY and… 4 min read · 8 Nov databases/pagination-why-next-page-shows-a-post-twice-and-how-a-bookmark-fixes-i.md Pagination: why 'next page' shows a post twice, and how a bookmark fixes it Why does "next page" sometimes show the same post twice, and why does page 90,000 load slowly? Both come from paging with LIMIT and OFFSET… 5 min read · 1 Nov databases/caching-why-the-second-read-is-fast-and-how-a-copy-goes-stale.md Caching: why the second read is fast, and how a copy goes stale Why does a page load slowly the first time and fast the second? Usually a cache: a quick copy of an answer the database already gave. Run a… 5 min read · 29 Oct databases/the-n-1-query-problem-why-a-page-that-asks-the-database-501-questions.md The N+1 query problem: why a page that asks the database 501 questions is slow A page that lists authors and their books can quietly send one query per author. With 500 authors that is 501 trips to the database, and… 4 min read · 26 Oct databases/transactions-why-a-money-transfer-must-save-both-changes-or-neither.md Transactions: why a money transfer must save both changes or neither Sending £50 between two accounts takes two changes to a database. If the power fails between them, the money vanishes. A transaction groups… 5 min read · 23 Oct databases/sql-indexes-why-finding-one-row-among-200-000-takes-about-18-steps.md SQL indexes: why finding one row among 200,000 takes about 18 steps A database with 200,000 customers can answer "find this email" by reading every row, or by reading about 18 entries. The difference is an… 4 min read · 20 Oct