SQL Fundamentals: Tables, Indexes, ACID
You'll learn to
- -Describe a table, a primary key and a foreign key, and why the database enforces them
- -Explain what an index is with the book-index picture, and say what it costs
- -Follow one bank transfer to see what each of the four ACID guarantees prevents
- -Say when a relational database is the right default, and what it costs you
Your three tiers are in place, and the data tier is the one that has to be right: when a server restarts, a user clears their browser or a deploy goes wrong, the database is the only place the facts still live. The relational (SQL) database is the first tool most teams reach for, and the reason is not habit. It makes a handful of promises that are very hard to keep with your own code. This chapter explains the three ideas behind those promises: tables with enforced rules, indexes that make lookups fast, and transactions that keep related changes together.
An Analogy: The Strict Librarian
Imagine a library run by a very strict librarian. Every shelf holds one kind of record in a fixed format: one shelf of member cards, another of loan slips. The librarian refuses to file anything that breaks the rules: a loan slip for a member who does not exist, two member cards with the same number, a card with the name left blank. The librarian also keeps a card catalog, so finding a record never means walking every shelf, and treats a multi-step task (hand over the book, stamp the slip, update the member's count) as one job that is either done in full or not done at all.
- -The shelves are tables, and each record is a row with the same columns as every other row in its table.
- -The strict librarian is the database engine, enforcing rules (constraints) so your application code cannot forget to.
- -The card catalog is an index.
- -A multi-step job that is done in full or not at all is a transaction, and the guarantees around it are called ACID.
Tables, Rows, and Keys
A table is a grid: every row has the same columns, and every column has a declared type. Two kinds of key tie the grids together. A primary key is a column whose value is unique in every row, such as an id, so that any row can be named exactly. A foreign key is a column whose value must match the primary key of a row in another table, which is how one table points at another. Here is a small shop:
CREATE TABLE users (
id BIGINT PRIMARY KEY,
email TEXT NOT NULL UNIQUE,
created_at TIMESTAMP NOT NULL
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
user_id BIGINT NOT NULL REFERENCES users(id),
total NUMERIC(10, 2) NOT NULL
);Read those declarations as promises. NOT NULL means every user has an email. UNIQUE means no two users share one. REFERENCES users(id) means an order can only be created for a user who exists, so inserting an order for user 999 when there is no user 999 fails, loudly, at the database. This rigidity is the point: the rule is enforced in one place, so every service and every script that touches the data obeys it, including the one somebody writes next year without reading your code.
The word relational comes from relating tables through keys. To see an order together with its buyer's email, you ask the database to join the two tables on the key that connects them:
SELECT orders.id, orders.total, users.email
FROM orders
JOIN users ON users.id = orders.user_id
WHERE orders.id = 1001;Indexes: A Card Catalog for Your Table
Now suppose the orders table holds 100 million rows and you want every order placed by user 42. With no help, the database has to read every row and check its user_id: 100 million checks to find perhaps a dozen matches. That is fine at 100 rows and ruinous at 100 million. It is the same problem as finding every mention of a topic in a thick book. You do not read it cover to cover; you open the index at the back, look the topic up, and jump to the pages it lists.
A database index works the same way. It is a separate, sorted structure, almost always a B-tree, that maps column values to the rows holding them. A B-tree is a tree whose nodes each hold hundreds of keys, so it is very wide and very shallow: with about 500 keys per node, three levels already reach 500 times 500 times 500 = 125 million entries. Finding user 42's orders therefore takes three or four steps down the tree instead of 100 million row checks.
CREATE INDEX idx_orders_user_id ON orders (user_id);Indexes are not free. Every index speeds up reads on that column but slows down writes (the index has to be updated too) and takes up additional storage. Indexing every column "just in case" is a common beginner mistake; index what you actually query by.
Transactions and ACID: One Bank Transfer
Some jobs are several steps that only make sense together. Moving $100 from Asha's account to Ben's is two writes: subtract 100 from Asha, add 100 to Ben. A transaction wraps both so the database treats them as one unit. The guarantees it gives are summed up by the acronym ACID, and each is easiest to understand by picturing it failing.
- -Atomicity: all or nothing. If the server crashes after Asha has been debited but before Ben is credited, the database undoes the debit when it restarts. Without atomicity, $100 would simply vanish.
- -Consistency: the data only ever moves from one valid state to another, as defined by your rules. If a rule says no balance may go below zero, a transfer that would leave Asha at minus $50 is rejected as a whole.
- -Isolation: transactions running at the same moment do not see each other's half-finished work. Suppose Asha has $100 and two $100 transfers start together. If both read her balance as $100, both would go ahead and she would end at minus $100. With isolation they behave as though one ran after the other, so the second sees a balance of $0 and is refused.
- -Durability: once the database says committed, the change survives a crash or power cut, because it was written to disk (to a log) before the answer was given, not merely held in memory.
When SQL Is the Right Default
Put the promises together and you can see where relational databases shine: money, stock, bookings and accounts, anywhere that probably correct is not good enough, and anywhere you will want to ask new questions of the data later without redesigning it. You pay for those promises with rigidity (changing the shape of a huge table takes care) and, as the next chapters show, with difficulty spreading one database's guarantees across many machines. Keep that cost in mind: it is the reason the NoSQL families exist.
In one line: a relational database is a strict librarian. Tables and keys refuse bad data, an index trades a little storage for fast lookups, and a transaction (ACID) makes a multi-step change all-or-nothing, isolated and durable.
Interview Signal is part of Pro
See a real weak answer next to a real strong one for this exact topic.
Quiz is part of Pro
Test what you just read with a short quiz, and bank the XP.
Discussion0
Join the Discussion
Sign in to leave comments, reply to others, or like insights.
No comments yet. Be the first to start the thread!