SQL Fundamentals: Tables, Indexes, ACID

Module 2 ended on a warning: the data tier is the hardest to scale, and almost everything from here forward is about relieving pressure on it. Before you can relieve that pressure, you need to understand what you're actually working with, so this module starts with the database most systems reach for first: the relational (SQL) database.

A relational database organizes data into tables: rows and columns, like a spreadsheet with rules. Every row in a "users" table has the same columns (id, email, created_at). Relationships between tables are explicit: an "orders" table has a user_id column that must point to a real row in "users" (a foreign key constraint). This rigidity is the whole point: the database itself refuses to store data that violates your rules, instead of leaving that job to application code that might forget.

Without help, finding a row means scanning the entire table, fine for 100 rows, ruinous for 100 million. An index is a separate, sorted structure (almost always a B-tree) that lets the database jump straight to matching rows instead of scanning everything, the same way a book's index lets you jump to a page instead of reading cover to cover.

• -Atomicity: a transaction either fully happens or fully doesn't. A bank transfer's debit and credit never happen halfway.
• -Consistency: the database only ever moves between valid states, per whatever rules (constraints) you've defined.
• -Isolation: concurrent transactions don't see each other's uncommitted changes, so two people checking out at once don't corrupt each other's orders.
• -Durability: once a transaction is committed, it survives a crash. The disk write actually happened, not just an in-memory promise.

These four guarantees are why SQL databases remain the default choice for anything involving money, inventory, or user accounts: places where "probably correct" isn't good enough.