Lesson 3.3 · 11 min read
You have tables. You have ids. You have queries that can follow an id from one table to another. Now we put those together into the thing that actually matters: designing how the pieces of your product's data relate to each other.
This is the craft. Not the syntax, not the tooling, this. Data modeling is where a product quietly becomes flexible or quietly becomes brittle, and the decisions are made early, in an hour, by someone who may not realize what they're deciding. Get it right and features you haven't imagined yet will slot in gracefully. Get it wrong and every future change fights you, until eventually someone proposes a rewrite, and the rewrite happens because of choices made in the first week.
Designers understand this intuitively, because you've lived the same story with component architecture. The difference is stakes: a badly structured component can be rebuilt on a quiet Thursday. A badly structured database has a million rows of live user data inside it. So let's learn to do this well.
Strip away the terminology and there's one mechanism underneath everything: a row in one table holds the id of a row in another table. That's a relationship. Everything else is a variation on where you put the id and how many there are.
We saw this in the last posts. A post holds an author_id, which is the id of a row in the users table. That single column is the entire relationship: this post belongs to that user. Follow the id and you find the person. Such a column, one that holds another table's id, is called a foreign key. Its own table's id is the primary key; a reference to someone else's is a foreign key. The names are just labels for "my id" and "a pointer to yours."
Once you see relationships as ids pointing at rows, the three shapes below stop being abstract categories to memorize and become obvious consequences of where the pointer lives.
By far the most common relationship in any product. One thing has many of another thing, and each of those belongs to exactly one.
One user has many posts. Each post has exactly one author. One project has many tasks. Each task belongs to exactly one project. One order has many line items. Each line item belongs to one order.
Now, the important question, and the one that reveals whether someone understands data modeling: where does the id go? It's tempting to think the user should hold a list of their post ids. But think about it practically. A user might have ten thousand posts. Cramming a growing list into a single column is awkward, unbounded, and hard to query. Whereas each post has exactly one author, always, forever, one value.
The id goes on the "many" side. The post holds the author_id. The task holds the project_id. The line item holds the order_id. Each row on the many side points at its one owner. This is not a convention someone chose arbitrarily; it falls directly out of the fact that "many" is unbounded and "one" is a single value that fits neatly in a column.
And this immediately gives you everything. Want one user's posts? Find all posts where author_id equals that user's id. Want a post's author? Follow its author_id to the users table, which is exactly the JOIN you learned. Both directions of the relationship are available from a single column sitting on the many side. That's the elegance of it, and it's why one-to-many is the workhorse of nearly every schema you'll ever read.
Sometimes the relationship runs both ways. A student takes many courses, and a course has many students. A post has many tags, and a tag is applied to many posts. A user belongs to many teams, and a team has many users.
Now where does the id go? Neither side works. A student can't hold a growing list of course ids in one column, and neither can a course hold its students. The relationship simply doesn't fit inside either table, because both sides are unbounded.
The answer is genuinely clever and, once seen, feels inevitable: you create a third table whose entire purpose is to hold the connections. Each row in it is a single link between one row on each side.
A enrollments table, with two columns: student_id and course_id. Sarah enrolled in Biology becomes one row: her id, and Biology's id. Sarah in Chemistry is another row. Marcus in Biology is another. The table is nothing but pairs, each pair one relationship. This is called a join table (also a junction or linking table), and now everything is answerable. Sarah's courses? Find rows in enrollments where student_id is hers, then follow each course_id. Biology's students? The mirror image. Both directions, cleanly, from a table that holds nothing but ids.
Here's where it gets genuinely interesting as a design matter, and where good modelers separate from adequate ones. That join table can hold more than just the two ids. When did Sarah enroll? What grade did she receive? Is her enrollment active? None of those facts belong to the student (they'd be different per course) or to the course (different per student). They belong to the relationship itself.
That's a real insight worth carrying. Some facts don't live on either thing; they live on the connection between them. An enrollment has a date and a grade. A team membership has a role (is this person an admin of this team, or a member?) and a joined date. Once you notice that the relationship can carry its own properties, you stop seeing a join table as plumbing and start seeing it as an entity in its own right, often one with a real name in your product's vocabulary. It isn't a users_teams table. It's a memberships table, and a membership is a genuine thing in the product, with its own attributes and its own meaning.
Recognizing that a relationship is actually an entity is one of the more sophisticated moves in data modeling, and it usually makes the model dramatically better.
One thing corresponds to exactly one other thing. A user has one profile. An order has one shipping address record.
This one is uncommon, and when you see it, it's worth asking why the two aren't simply one table. Often they should be. If a user has exactly one profile with a bio and an avatar, those could just be columns on the user.
There are genuine reasons to split, though, and knowing them tells you when it's right. Sometimes it's about sensitivity: keeping payment details or authentication secrets in a separate table with tighter access, so a query that fetches a user can't casually return them. Sometimes it's about size: a huge blob of data that's rarely needed shouldn't be dragged along every time you fetch a user. Sometimes it's about optionality: if only two percent of users ever have a certain set of fields, those fields being null on ninety-eight percent of rows is a signal they belong elsewhere.
So one-to-one is less a workhorse and more a deliberate choice with a reason behind it. When you see one in a model, ask what the reason is. If there isn't one, it's probably two tables that want to be one.
Almost every genuinely bad data model I could show you fails in one of two ways, and they're opposites. Knowing both lets you catch a model going wrong in either direction.
The first failure is duplication. The same fact stored in more than one place. This is the author_name copied onto every post from our earlier example. The moment a fact lives in two places, those places can disagree, and eventually they will. The user changes their name, one place updates, the other doesn't, and now your product contains two contradictory truths and no way to know which is right. This is why the instinct to "just store a copy, it's easier" is so dangerous. It's easier exactly once, at the moment you write it, and then it's harder forever.
The principle underneath is worth stating plainly: every fact should live in exactly one place, and everything else should point at it. The user's name lives on the user. Posts point at the user. Change it once, and every post is correct instantly, because no post ever held a copy to begin with. When you're reviewing a model, hunt for the same fact appearing in two tables. It's the single most reliable smell of a model that will cause pain.
The second failure is the opposite: cramming unrelated things into one table. A users table with columns for company_name, company_address, and company_tax_id. If ten users work at the same company, that company's address is now stored ten times, which is duplication again, arriving from the other direction. The company is a separate thing. It deserves its own table, and users should hold a company_id.
The tell for this failure is a table where some columns describe one thing and other columns describe something else that has an independent existence. If you can point at a group of columns and name what they describe, and that name isn't the table's name, you've found a table that wants to be two.
Both failures come from the same root: the model doesn't match the actual structure of the domain. The fix is always the same. Find the real things, give each its own table, connect them with ids, and let every fact live exactly once.
Here's the part that should feel familiar, because it's the discipline you already have, aimed at a new target.
Designing a data model starts with the same move as designing anything: understand the domain truthfully. Not the screens, not the current feature request. The actual things that exist and how they genuinely relate. When you sketch a model, you're making claims about reality. "A post has exactly one author" is a claim. Is it true? What about co-authored posts? "A user belongs to one team" is a claim. Will that survive contact with a customer who has people in two teams? Every relationship you draw is a statement about how the world works, and the model will hold you to it.
This is why modeling surfaces product questions nobody has answered. You cannot decide whether it's one-to-many or many-to-many without deciding what the product actually allows, and often nobody has decided. The model forces the conversation. That's not a delay; that's the most valuable thing modeling does, and it happens before a line of code is written, when changing your mind is free.
Then think ahead, carefully but not paranoidly. You are not trying to predict every future feature. You're trying to avoid choices that are gratuitously hard to reverse. Storing a reference instead of a copy costs nothing today and buys you enormous flexibility. Making something a separate table rather than a column costs little and preserves your options. These aren't speculative over-engineering; they're the small structural decisions that keep the future open. Meanwhile, genuinely speculative complexity, building a many-to-many because maybe someday, usually isn't worth it. The judgment is knowing which is which, and it improves the more models you look at.
Ask an AI to build a feature and it will create tables for you. They'll be plausible. They'll work for the exact thing you asked. And they'll be designed with knowledge of one request rather than knowledge of your product.
You know things the AI does not. You know a user will eventually be able to belong to multiple workspaces. You know display names will be editable. You know that next quarter, comments need to be attachable to more than just posts. Every one of those is a modeling decision that's trivial today and painful later, and every one is invisible to whoever is only looking at the current ticket.
So when the AI proposes a schema, read it as a designer reads a wireframe: not for whether it works, but for what it assumes. Does this store a copy of something that should be a reference? Does this table hold columns describing two different things? Is this a one-to-many that will become many-to-many the moment a real customer arrives? Does this relationship carry properties that need somewhere to live, meaning the join table is really an entity? Should this join table have a proper name because it's a real thing in the product?
Then direct it precisely: "make comments reference the user by id rather than storing the name," "this should be a many-to-many with a memberships table, and memberships need a role column," "pull the company fields out into their own table with a company_id on users." That's not fussing over details. That's owning the single most expensive-to-change decision in the application, using knowledge only you have.
The data model is the skeleton of a product. Everything is built on it, nothing can move without it, and it is the last thing anyone wants to replace. Designing it well is not database administration. It's architecture, and it's design, and it's yours.
Do this before the next post
Take the sketch you made at the end of the last post and now do the harder half: draw the relationships properly, and then attack your own model.
For every connection between two boxes, decide which shape it is. Is it one-to-many, and if so, have you put the id on the many side? Is it many-to-many, and if so, have you drawn the join table that has to exist? And for each join table, ask the good question: does this relationship have properties of its own, a role, a date, a status, and if so, does it deserve a real name in your product's language?
Then hunt for the two failures. Scan every table for a fact that also appears somewhere else, any copy of a name, a price, an address that lives in two places. And scan every table for a group of columns that describe something other than what the table is named for. Fix each one you find.
Finally, take one relationship you drew and challenge the claim it makes. If you drew "a task belongs to one project," ask what happens when someone wants a task in two projects. If you drew "an order has one customer," ask about gift orders. You're not obligated to support every case. You're obligated to decide, consciously, rather than to discover the decision later, encoded in a schema by someone who didn't realize they were making it. That deciding, done deliberately and early, is the whole craft.