This series follows a fictional conversation between an experienced engineer and his nephew. Every episode explores one stage of how software moves from an idea to production.
👦 Nephew: Uncle, the service is designed. Functions, flow, the split between Service and Repository. Now, finally, the database?
👨🦳 Uncle: Finally, the database. And I want you to unlearn something before we start — a table is not "just where data sits." Every choice you make here outlives almost everything else we've designed. Code gets refactored constantly. A production table with real user data in it gets refactored rarely, carefully, and painfully. Get this wrong, and you're not fixing a bug — you're running a migration on a table with a million live rows, at 2 AM, hoping nothing breaks.
👦 Nephew: That sounds dramatic for a table with four columns.
👨🦳 Uncle: Let's find out if it's dramatic. Look at what we sketched loosely back in Episode 4.
wishlist_items
id
user_id
product_id
created_at
Every single one of those four things — I want you to be able to defend, not guess.
Model: What Are We Actually Storing?
👨🦳 Uncle: First question, before any column names — should this even be a relational table? Or does Wishlist belong in a document store, key-value store, something else?
👦 Nephew: I don't know. When do you not use a relational database?
👨🦳 Uncle: When your data doesn't have real relationships worth enforcing, or when your access pattern is almost entirely "fetch this one document by its key," with no need to join across entities. Think about what we're actually building here — a wishlist item only makes sense if both the user and the product it points to actually exist. If someone deletes a product tomorrow, you immediately have to ask what happens to every wishlist entry pointing at it. That question — what happens on one side when the other side changes — is exactly what a relationship is. A relational database is built to protect that. Wishlist fundamentally connects two other things that already exist as rows: a user and a product. Don't choose a database because it's fashionable. Choose it because it matches your data.
Constrain: The Keys That Protect You
👨🦳 Uncle: Now the primary key — id. In Episode 4 I mentioned UUID versus auto-increment as a real trade-off. Let's actually decide it this time, properly.
Option A: id BIGINT AUTO_INCREMENT
Option B: id UUID
👦 Nephew: Auto-increment is smaller, faster to index. UUID doesn't leak how many rows exist, or let someone guess other IDs by counting.
👨🦳 Uncle: Good, you remembered. Now here's the piece that actually decides it for Wishlist specifically — where does this ID get created? If it's created by the database, on a single server, auto-increment works fine. But the moment you have multiple services, or you ever need to create a wishlist item before it reaches the database — say, generating it client-side for an offline-first mobile experience — auto-increment can't help you, because only the database knows the next number. A UUID can be generated anywhere, safely, with no coordination. For most CRUD tables, either works. But knowing why you're picking one, not just which is more popular, is the actual skill.
👦 Nephew: For Wishlist specifically?
👨🦳 Uncle: Honestly — for this feature, either choice is defensible. It's user-facing data, referenced in API responses, no real need for offline generation yet. Auto-increment would work fine, and plenty of high-scale systems deliberately use auto-increment internally, for good reasons. The important part isn't picking the "correct" one — it's understanding the trade-off, because different teams make different calls here depending on scale, tooling, and operational needs. If I had to choose today, I'd lean UUID, mainly so nobody downstream ever builds a feature assuming IDs are sequential and countable — but that's a judgment call, not a rule.
Foreign Keys, and the Question From Episode 1
👨🦳 Uncle: Now user_id and product_id. Both should be foreign keys — but a foreign key is more than a reference. It's a rule about what happens when the thing it points to disappears.
product_id UUID
FOREIGN KEY → products.id
ON DELETE ???
👦 Nephew: Wait — this is Episode 1's question. "What if the product gets deleted by the admin tomorrow?"
👨🦳 Uncle: The exact same question. Except back then it was a product decision. Now it's a database decision, and the database forces you to actually pick an answer — it won't let the column stay ambiguous.
ON DELETE CASCADE → deleting the product silently deletes the wishlist row too
ON DELETE SET NULL → wishlist row stays, product_id becomes null
ON DELETE RESTRICT → deleting the product is blocked while it's still wishlisted
👦 Nephew: Which one is right?
👨🦳 Uncle: Depends entirely on what Episode 1's business answer was. If the PM said "show it as unavailable," CASCADE is wrong — it would erase the user's wishlist entry entirely instead of just marking it unavailable. SET NULL breaks a NOT NULL foreign key — so we'd either have to allow product_id to be nullable, or choose a different strategy entirely. Realistically, you'd keep product_id NOT NULL, skip a hard foreign key delete rule, and instead soft-delete products — mark them inactive rather than actually removing the row. The database decision and the product decision have to agree with each other, or one of them is silently wrong.
👦 Nephew: So I can't design this table without going back and rereading the requirements from Episode 1.
👨🦳 Uncle: That's exactly the point. Every stage of this series has been building toward this moment — a database schema is where every earlier decision either gets honored, or quietly contradicted.
The Constraint That Replaces a Function
👦 Nephew: What about the duplicate check? In Episode 4, checkDuplicate() lived in the service.
👨🦳 Uncle: It still does — but remember what we said about race conditions. The service check is your first, fast rejection. The real guarantee lives here.
UNIQUE (user_id, product_id)
👦 Nephew: One line, and it's actually enforced no matter what the application code does.
👨🦳 Uncle: That's the difference between a rule you hope holds, and a rule that cannot be violated, even by a bug, even by two requests landing at the same microsecond.
Normalize, or Snapshot?
👨🦳 Uncle: Now the trickiest decision. Should this table store just product_id — or also a copy of the product's price and name at the moment it was wishlisted?
👦 Nephew: Why would we duplicate data that already lives in the products table?
👨🦳 Uncle: Remember Episode 3 — the price-drop notification feature we teased? To know a price dropped, you need to know what the price was when the user wishlisted it. If you only store product_id, that historical price is gone the moment the live price changes — you're always looking at "now," never "then."
Normalized:
wishlist_items(user_id, product_id)
→ always join to products for current data
→ simple, never goes stale, but no memory of the past
Snapshot:
wishlist_items(user_id, product_id, price_at_add)
→ remembers what mattered at the time
→ but now this table itself needs to stay meaningfully accurate
👦 Nephew: So it's not "which one is correct." It's "which one does the feature actually need."
👨🦳 Uncle: Exactly. Design the database for the requirement in front of you, not for every requirement you can imagine. If price-drop notifications are still just a tease, not a committed feature, don't add price_at_add today speculatively — that's a column you'll maintain forever for a feature that might never ship. Add it when the requirement is real, the same way we'd handle any hidden question in Episode 1 — ask, don't assume.
Scale: The Index Nobody Adds Until It's Too Late
👨🦳 Uncle: Last question. listItems(userId) — what does that query actually do to this table?
SELECT * FROM wishlist_items WHERE user_id = ?
👦 Nephew: Looks up rows matching a user. Simple.
👨🦳 Uncle: Simple, until this table has ten million rows across all users, and there's no index on user_id. Then that query scans the entire table, every single time, for every single user opening their wishlist page.
CREATE INDEX idx_wishlist_user ON wishlist_items(user_id)
👦 Nephew: Doesn't the UNIQUE (user_id, product_id) constraint already index user_id, since it's the first column?
👨🦳 Uncle: Good — that's actually correct for most databases; a composite unique constraint typically also serves lookups on its leading column. Which means for this specific table, you may not need a second index at all. That's worth checking, not assuming — because an index you don't need still costs you something. Before adding any index, always ask: which specific query is this actually speeding up? If you can't name the query, you don't need the index yet.
👦 Nephew: What does an index cost?
👨🦳 Uncle: Every index speeds up reads on that column, and slows down every single write, because the database has to update the index too. A table with ten unnecessary indexes writes slower for no reason. Indexing isn't "add more, be safe." It's "add exactly what your real query patterns need, and nothing extra."
Model → Constrain → Scale
👦 Nephew: The framework?
👨🦳 Uncle:
Model
↓
Constrain
↓
Scale
Model — decide what kind of storage the data's actual shape calls for, and whether to normalize or store a snapshot, based on what the feature genuinely needs — not on every future feature you can imagine.
Constrain — every key, foreign key, and unique constraint is a rule, not decoration. Each one should trace back to a real answer from your requirements, not a guess made while writing the schema.
Scale — index for the queries you actually run, verify what you already get for free from existing constraints, and remember every index you add also has a cost on every write.
Uncle's Line
"A wrong function gets refactored in an afternoon. A wrong column gets migrated in a maintenance window, on a table full of real users, with everyone hoping nothing breaks."
👦 Nephew: So the database isn't just storage. It's where every earlier decision either gets enforced, or exposed as incomplete.
👨🦳 Uncle: That's exactly it. Now — you have the service designed, and the table designed. You still haven't decided the exact shape of what the outside world sends and receives to trigger all of this. That's next.
🧠 Think Like an Engineer — Homework
Take the same feature you've been designing since Episode 3.
- Model — is its core data relational, or does it genuinely fit a different kind of store? Would you normalize it, or does some part of it need a snapshot in time?
- Constrain — pick one foreign key it would need. Decide what should happen when the row it points to gets deleted, and defend your answer using a requirement from Episode 1's kind of thinking.
- Scale — write the one query this feature will run most often. Does it need an index that doesn't already exist as a side effect of another constraint?
Don't worry about picking the "textbook correct" answer. The goal is to practice noticing that a database schema is a set of promises, not just a set of columns.
— End of Episode 5 —
Top comments (1)
I really like this conversation format—it makes database design much less intimidating for beginners. Looking forward to seeing how you justify each column, especially id and created_at.