Design: a social feed schema
Time to put the whole DBMS toolkit together by designing a real thing: the database behind a tiny social app. People sign up, write posts, like each other's posts, and follow each other. We'll build the tables one at a time, and at every single step we'll name the exact concept doing the work — primary key, foreign key, normalization, the right index. By the end you'll have a complete schema and know WHY every choice was made. Let's design it.
Step 1 — The Users table (primary key)
Every social app starts with people, so start with a Users table. The columns are the facts about a user: id, name, maybe a join date. One row per person. The most important column is id — the PRIMARY KEY. It's a unique number (1, 2, 3…) that names exactly one user forever, even if they change their name. Why not use the name as the identifier? Because two people can both be named 'Maya' — names aren't unique, ids are. From here on, whenever any other table needs to say 'this belongs to that user', it will store this id. The whole schema hangs off this one column.
Step 2 — The Posts table (foreign key + normalization)
Now people write posts, so add a Posts table: id (its own primary key), the text, a timestamp, and — crucially — a user_id column saying WHO wrote it. That user_id is a FOREIGN KEY: it stores the author's id from Users, not a copy of their name. This is NORMALIZATION in action — the wrong way would be to copy 'Maya, joined 2024' into every post she writes, so her details get duplicated a thousand times and can disagree with themselves if she ever changes them. The relational way stores Maya's details ONCE in Users; every post just points at her by id. Change her name in one place and all her posts reflect it, because they never stored a copy. One user, many posts — a classic one-to-many relationship, expressed by a single foreign-key column.
Step 3 — The Likes table (a many-to-many link + composite key)
Likes are trickier: one user can like many posts, AND one post can be liked by many users. That's a MANY-TO-MANY relationship, and you can't capture it with a single column on either table (a post can't list all its likers in one cell — that breaks the tidy table shape). The answer is a LINK TABLE: a Likes table whose whole job is to record connections. Each row is just two foreign keys — user_id (→ Users) and post_id (→ Posts) — meaning 'this user liked this post.' One like = one row. To find everyone who liked a post, grab the Likes rows with that post_id. Its primary key is the PAIR (user_id + post_id) together — a COMPOSITE KEY — because that pair is what must be unique: a person can't like the same post twice, but the same person and the same post can each appear in many other rows.
Step 4 — The Follows table (a self-referencing many-to-many)
Following is the brain-bender, and it's beautiful. A user follows other users — so this many-to-many relationship is Users to USERS, the table pointing at ITSELF. That's a SELF-REFERENCING relationship. Use the same link-table trick: a Follows table with two columns, follower_id and followee_id — both foreign keys into Users. A row 'follower_id=1, followee_id=2' means 'user 1 follows user 2.' The pair is the composite primary key (you can't follow the same person twice). Note the two columns have different MEANINGS even though both point to Users — direction matters: 1 following 2 is not the same as 2 following 1. One little table captures the entire follow graph of the whole app.
Step 5 — Make the feed fast (the right index)
Now the read that matters most: showing a user's profile means fetching all of THAT user's posts. Without help, the database would scan every post in the whole app checking 'is user_id = 7?' — a full O(n) scan that gets slower as the app grows. So we add an INDEX on Posts.user_id. As you learned, that index is a B-tree: the database jumps straight to that user's posts in a few hops (O(log n)) instead of reading every post. We'd similarly index Likes.post_id (to count a post's likes fast) and Follows.follower_id (to list who someone follows). We index exactly the columns we SEARCH by — and we accept the small write cost, because reads on a feed vastly outnumber writes. That's the index lesson and the query-execution lesson, applied right where they pay off.
Step 6 — The whole schema, and why each choice
Step back and see it whole. USERS holds each person once (primary key id). POSTS holds each post, with a foreign key user_id pointing at its author — one fact stored once, normalized. LIKES is a link table turning the user-likes-post many-to-many into rows, keyed by the composite (user_id + post_id). FOLLOWS is a self-referencing link table for user-follows-user, keyed by (follower_id + followee_id). And indexes on the foreign-key columns we read by (Posts.user_id, Likes.post_id, Follows.follower_id) make the feed fast. Every piece earns its place: primary keys give each thing an identity, foreign keys connect things without copying, link tables express many-to-many, normalization keeps each fact in one place so nothing contradicts itself, and indexes turn the important reads from slow scans into fast jumps. That's a complete, correct, fast schema — built from exactly the concepts you learned.
Questions you might have
▸Why can't a Like just be a column on Posts, like a list of who liked it?
Because a table cell is meant to hold ONE value, not a growing list — cramming 'liked by: 1, 5, 9, 12, …' into one cell breaks the tidy table shape and makes it impossible to search or count properly. A many-to-many relationship always needs its own LINK TABLE, where each connection (this user liked this post) is its own clean row.
▸What's a 'composite key' and why does Likes need one?
It's a primary key made of TWO columns together instead of one. In Likes, neither user_id alone nor post_id alone is unique (a user likes many posts; a post has many likers), but the PAIR (user_id + post_id) is — it appears at most once, because a person can't like the same post twice. So the pair, taken together, is the unique identity of a like.
▸How can a table have a foreign key pointing at ITSELF (Follows)?
Nothing stops it — a foreign key just points at some row's primary key, and that row can live in the same table. Follows has follower_id and followee_id, both pointing into Users, because a follow connects one user to another user. The table referencing itself is exactly how you model 'things of one kind relate to other things of the same kind' — like users following users.
▸Why index user_id on Posts but not every column?
Because we SEARCH by user_id constantly — every profile view fetches that user's posts — so the index pays for itself by turning a full scan into a fast jump. Columns we rarely search by aren't worth indexing: each index costs storage and slows writes (the B-tree must be kept balanced). The rule from the index lesson holds: index the columns you read by, not all of them.
▸Isn't all this splitting into tables more complicated than one big table?
It looks like more pieces, but it's actually simpler to keep CORRECT. One giant table would copy each user's details into every post and like, so updating a name means hunting down dozens of copies — and missing one means the data contradicts itself. Separate tables linked by id store each fact once, so there's nothing to keep in sync. The structure is the thing that keeps a growing app trustworthy.
Best read after: Keys & relationships, How indexes work (B-trees)