Storing data: tables, keys, SQL, documents and the three kinds of storage

Servers forget; databases remember. Almost every design question ends up being about data: how it's shaped, how it's found again, and what happens when two people change it at once or a machine crashes mid-write. This module covers the basics you need before the data modules (11 to 17): tables and keys, enough SQL to read a query, other data shapes, transactions, how data is turned into bytes, and the difference between block, file and object storage.

1. The idea

Why not just write files? You can, and for logs and images that's exactly what we do. But for records that many users read and change at once, a database gives you four things files don't: safe concurrent changes, crash safety (a half-finished write is undone), queries ("all orders over $50 this week") and indexes to answer them fast.

SQL in six statements

CREATE TABLE users  (id BIGINT PRIMARY KEY, name TEXT, city TEXT);
CREATE TABLE orders (id BIGINT PRIMARY KEY, user_id BIGINT REFERENCES users(id),
                     amount NUMERIC(10,2), status TEXT);

INSERT INTO users (id, name, city) VALUES (1, 'Ada', 'London');
SELECT name, city FROM users WHERE city = 'London';
UPDATE orders SET status = 'shipped' WHERE id = 3;
DELETE FROM orders WHERE status = 'cancelled';

SELECT u.name, o.amount                      -- a join: combine two tables
FROM orders o JOIN users u ON u.id = o.user_id;

2. How it works

The same order, stored three ways

How you shape data decides which questions are cheap. Press Play to see one customer and their orders as relational tables, a document and key-value pairs.

All or nothing: a transaction

Moving $50 from Ana to Ben is two writes. If the server crashes between them, money vanishes, unless both writes are one transaction.

Block, file and object storage

Below every database and file there's storage of one of three kinds, and they offer very different operations.

3. Try it yourself

Run some SQL

A tiny shop database with two tables. Pick a query to see what it returns and how it works.

Text vs binary: what one record costs

The same user record encoded as JSON and as Protobuf (a binary format). Change the values and watch the bytes. Protobuf's numbers are exact: it's the real encoding.

Protobuf schema used: message User { int64 id = 1; string name = 2; string city = 3; int32 age = 4; bool verified = 5; }, with city "London" and verified true.

4. Trade-offs

5. In real systems

Most products keep their core data in a relational database (PostgreSQL or MySQL, often managed by a cloud provider), add Redis as a cache, put files in object storage, and add specialised stores (search, analytics) only when a need appears. The data modules go deeper:

Say it in the interview

6. Remember it

7. Common mistakes

8. Design exercise (25 minutes)

1. Model a blog (10 min). Tables for users, posts, comments and likes, with primary and foreign keys. Which columns need an index?
2. Write the queries (10 min). A post with its comments and authors; the ten most-liked posts this week; all comments by one user.
3. Find the transaction (5 min). Which action in the blog needs several writes to happen together?

9. Self-check quiz

10. Go deeper