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)
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…
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…
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…
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…
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…
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…