The 'Should This Be Its Own Table?' Test

The single most common schema decision

Every new feature, every new product idea, every new piece of data eventually runs into the same question: does this need its own table, or can it just be a column on something else?

Get this call wrong in either direction and the cost shows up later. Over-split schemas have ten tables doing the work of three, with joins everywhere and a cognitive overhead that never goes away. Under-split schemas have giant tables full of unrelated columns that all change together for no good reason.

There's no perfect rule, but there is a workable test. Four questions, asked in order, that resolve most cases.

Question 1: Does this thing have its own lifecycle?

The first question is whether the data has a life of its own. Can it be created, updated, or deleted on a timeline that's different from the thing it's attached to?

A user's email address doesn't have its own lifecycle. It's created when the user is created, deleted when the user is deleted, and exists only as a property of the user. That's a column.

A user's posts have their own lifecycle. They're created and deleted independently of the user. A user can exist without any posts; posts can be edited and deleted while the user stays put. That's a table.

The test is sharp. If the answer is "this thing is born and dies with its parent," it's a column. If the answer is "this thing comes and goes on its own schedule," it's a table.

Question 2: Are there going to be many of these per parent?

The second question is about cardinality. Does a single parent row have many of these things, or just one?

A user has one email address (in most apps). A user has many posts. The "many" answer points straight at a separate table, because the only way to store "many of something per row" is with foreign keys pointing at the parent — which is the one-to-many shape from page 1 of this module.

"One per parent" doesn't automatically mean column. A user has one profile, and that can still be its own table for the reasons covered in the one-to-one section. But "many per parent" almost always means table.

Question 3: Will I want to query this independently?

The third question is about how the data will actually be used. Will you ever want to ask the database a question about these things on their own, without going through the parent?

"Show me every comment posted today, across all users" is a question that only works if comments live in their own table. If comments were jammed into a JSON blob on the user row, the database would have no way to answer efficiently.

"Show me every appointment scheduled this week, across all clients" is the same shape. Appointments need to be queryable on their own, so they need their own table, even though every appointment also belongs to a client.

A test that's almost as good: do I want this thing to appear in its own list view in the UI? If yes, it probably wants to be its own table.

Question 4: Does this carry data of its own?

The last question catches a different kind of case: when the thing being modeled is really a relationship, and the relationship itself has attributes.

A user being a member of a group is a relationship. If all the system needs to know is "yes or no, is this user in this group?", a simple join table with two foreign keys is enough. But the moment the relationship needs attributes — when the user joined, what their role in the group is, whether their membership is active — the relationship has data of its own, and the join table starts becoming a real entity in the system.

-- A pure join table — just the connection.
CREATE TABLE group_members (
  user_id  UUID REFERENCES users(id),
  group_id UUID REFERENCES groups(id),
  PRIMARY KEY (user_id, group_id)
);

-- The same table with a real lifecycle of its own.
CREATE TABLE memberships (
  id          UUID PRIMARY KEY,
  user_id     UUID REFERENCES users(id),
  group_id    UUID REFERENCES groups(id),
  role        TEXT NOT NULL DEFAULT 'member',
  joined_at   TIMESTAMPTZ NOT NULL DEFAULT now(),
  is_active   BOOLEAN NOT NULL DEFAULT true,
  UNIQUE (user_id, group_id)
);

Both are technically join tables, but the second one is also a first-class entity. It has its own id, its own columns, and queries that treat it as the subject of the question ("how many active memberships do we have?"). The moment a join table needs attributes, it deserves the upgrade.

Putting the questions together

The four questions, in order:

1. Lifecycle: Does this come and go independently?
2. Cardinality: Will there be many of these per parent?
3. Queryability: Will I want to ask about these on their own?
4. Its own data: Does this carry attributes beyond the connection itself?

If the answer to any of these is yes, the thing is leaning toward being its own table. If none of them are yes, the thing is probably a column. Most close calls have a clear answer once you walk down the list.

The other direction: when splitting was the wrong call

Splitting isn't free either, and over-splitting is its own mistake. A few real signs that two tables should have been one:

• The two tables are always queried together. Every read joins them; nothing ever reads one without the other.
• The two tables have highly overlapping columns that mean the same thing in both places — first_name, email, status — and the only difference between them was a category field that should have been a column.
• The two tables were created because they seemed like different categories of the same thing (e.g., "active clients" and "archived clients"), and that category turned out to be an attribute, not an entity.

Merging tables back together is a real and reasonable move. The cost is a migration to do the merge cleanly, and that's it. The schema getting simpler is a sign of progress, not a confession of failure.

The default lean

When in doubt, lean toward splitting. The reason is asymmetry: it's much easier to merge two tables into one later than to split one table into two. Merging is mostly an UPDATE plus a DROP TABLE. Splitting is a careful migration where every row of the old table has to be reassigned to one of the new tables, with foreign keys updated everywhere.

"Split-first, merge-if-needed" tends to leave fewer scars than "combine-first, split-when-it-breaks." That's the default lean for most experienced developers, and it's a reasonable default to inherit.

The next page covers the situation where splitting is the right call by every measure, and you still choose to break the rule on purpose for a specific read-performance reason. That's denormalization.