Why does a blog system need to be split into 6 tables?

A friend sent me the database directory of a blog backend and said, "Help me look at these table creation statements."

I glanced at them and noticed one thing: Almost every novice DBA writes one extra line of code on this table.

It's this "likes table":

CREATE TABLE `user_like_post` (
  `userId` int(11) NOT NULL,
  `postId` int(11) NOT NULL,
  PRIMARY KEY (`userId`, `postId`),   -- 🔑 composite primary key
  KEY `postId` (`postId`),            -- ⚠️ Is this line really needed?
  ...
)

90% of people will add a KEY userId (userId) below it, always feeling uneasy without a separate index on userId.

But it's redundant — a pure waste of disk space.

Why? The answer lies in the first design decision of this table. But to explain it clearly, we need to tell the whole story of "why split into 6 tables" from the beginning. This article uses a real blog system to work backwards from business requirements to answer one thing: How exactly should tables be split, and should an index be created or not.

---

First, look at the business: what data does a blog have

A typical blog (like a community such as Juejin) has this data: articles, likes, bookmarks, comments, users, avatars, tags.

Let's guess how the first version would be designed. A beginner's favorite move — stuff everything into one table:

❌ Wrong example: articles + users + comments + likes all stuffed into one big table

CREATE TABLE `everything` (
  id, title, content,
  userName, userPassword, userAvatar,
  commentContent, commentUserId,
  likeUserId,
  ...
)

It looks like "one table handles all queries," so convenient. But once the user base grows:

All queries go through one big table
    │
    ├─ Query article details → must scan super-wide rows (including comments, likes)
    │
    ├─ Query user info → also full table scan
    │
    ├─ Data reaches millions of rows → single table gets bigger and bigger
    │
    └─ Want to scale horizontally/shard → one big table, nowhere to start

The table is too wide, coupling is too tight, both querying and scaling are hard. Core goal: break the big table into smaller ones.

So how exactly do you split? Remember one sentence: The basis for splitting tables is "business entities" and "relationships between entities," not "just make it fast."

---

Step 1: Split each independent entity into its own table

First, distinguish "entities" from "relationships."

Entities: users, articles, tags, images — they are independently existing "things," each gets its own table. Relationships: likes, bookmarks, comments, articles↔tags — they are "relationships between two entities," split into relationship tables.

Let's start with the most core user table, whose decisions are the most interesting.

User table: why only store core fields?

The user table is key to all tables:

CREATE TABLE `user`(
  `id` int(11) NOT NULL AUTO_INCREMENT,          -- primary key, auto-increment
  `name` varchar(255) NOT NULL,                   -- unique, no duplicate names
  `password` varchar(255) NOT NULL,               -- note: cannot store plaintext
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4_unicode_ci

Notice, the user table has only 3 fields. Where's the avatar? The slogan (signature)? The personal bio?

None of them are here.

This isn't laziness, it's deliberate. The reason is simple: The user table is the most frequently queried table in the entire system — almost every request needs to verify login and bring out user info. The smaller it is, the more cache-friendly, the faster the queries, and the easier it will be to shard later.

Counter-intuitive point: Data isn't "put it in if you can," but "put it in only if there's a reason." Low-frequency, independent data like avatars are split out separately, using an avatar table + foreign key association.

CREATE TABLE `avatar` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `filename` varchar(255) NOT NULL,
  `size` int(11) NOT NULL,
  `userId` int(11) NOT NULL,           -- 🔑 foreign key, references user
  PRIMARY KEY (`id`),
  KEY `userId` (`userId`),             -- regular index: query avatar by userId
  CONSTRAINT `avatar_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4_unicode_ci

userId is a foreign key. It achieves two things:

Foreign key FOREIGN KEY
    │
    ├─ Ensures consistency: cannot add an avatar for a non-existent user
    │
    └─ Creates a regular index: fast queries for avatar by userId

A small piece of knowledge: The avatar image itself is not stored in the database; what's stored is filename (static resource path). Images go on OSS / CDN static servers, the database only records the "location." High-traffic images are distributed via CDN for proximity, and should not burden the database.

---

Step 2: Article table — another foreign key, another 'relationship'

The article table also relates to users (an article belongs to an author):

CREATE TABLE `post` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `content` longtext,                  -- 🔑 body is long → longtext
  `userId` int(11) DEFAULT NULL,       -- 🔑 author, foreign key
  PRIMARY KEY(`id`),
  KEY `userId` (`userId`),
  CONSTRAINT `post_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4_unicode_ci

Up to here, it's all familiar patterns: entity tables + foreign key associations. The real test is the "relationship" tables — especially that "likes table."

---

Step 3: Likes table — the space-saving wisdom of composite primary keys

A like is a relationship of "a user liked a certain article." It has a natural constraint: a user can only like the same article once.

Someone who gets it instantly will write:

CREATE TABLE `user_like_post` (
  `userId` int(11) NOT NULL,
  `postId` int(11) NOT NULL,
  PRIMARY KEY (`userId`, `postId`),  -- 🔑 composite primary key
  ...
)

Making (userId, postId) a composite primary key — the database directly guarantees "the same person cannot like the same article twice," no need to even write duplicate-checking business code.

Now back to the opening question: Do I need to create a separate index on userId?

The conclusion is no. This is InnoDB's most counter-intuitive, and also easiest space-saving, feature.

To understand it, you must first understand InnoDB's clustered index:

InnoDB clustered index mechanism
    │
    └─ Each table's primary key (PRIMARY KEY) is a clustered index
         │
         └─ Index leaf nodes = entire row data, all stored in primary key order

The composite primary key is (userId, postId), so the leftmost first column of the clustered index is userId.

And there's an iron rule in database indexing: Any query starting from the leftmost column can hit this composite index.

When querying by userId, it hits the leftmost column userId, so the composite primary key itself can cover "query likes by user". Creating a separate KEY userId is completely redundant, pure wasted space.

Composite primary key (userId, postId) clustered index
    │
    ├─ Query by userId  ✅ hits leftmost column, covered
    │
    └─ Core rule: leftmost prefix principle

So the final correct version of this table should be:

✅ Correct example: likes table

CREATE TABLE `user_like_post` (
  `userId` int(11) NOT NULL,
  `postId` int(11) NOT NULL,
  PRIMARY KEY (`userId`, `postId`),  -- composite primary key, already covers userId queries
  KEY `postId` (`postId`),           -- postId is not leftmost in composite key, so needs separate index
  ...
)

Golden rule: To decide whether a field needs a separate index, first check if it's the "leftmost column of some composite index" — if so, it's likely already covered.

Pitfall alert: If the business need is "query who liked a post by article," then postId does need a separate index, because it's not the leftmost in the primary key. Adding KEY postId to the likes table like this is correct.

---

Step 4: Comments table — comments on comments, using self-referencing

Comments have a characteristic: comments can be replied to with comments, unlimited levels. This is a tree structure.

The most straightforward modeling is self-referencing foreign key — same table, parentId points to its own id:

CREATE TABLE `comment` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content` longtext,
  `postId` int(11) NOT NULL,   -- belongs to which article
  `userId` int(11) NOT NULL,   -- who commented
  `parentId` int(11) DEFAULT NULL,  -- 🔑 comment on comment: points to itself
  PRIMARY KEY(`id`),
  KEY `postId` (`postId`),
  CONSTRAINT `comment_ibfk_3` FOREIGN KEY (`postId`) REFERENCES `post` (`id`) ON DELETE CASCADE,
  CONSTRAINT `comment_ibfk_2` FOREIGN KEY (`parentId`) REFERENCES `comment` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4_unicode_ci

A comment with parentId = NULL is a top-level comment; parentId = some comment's id is a reply to that comment.

A top-level comment → parentId = NULL
        │
        └─ A reply → parentId = top-level comment's id
                │
                └─ Its reply → parentId = that reply's id
                    (same table, pointing to itself)

Pitfall: parentId uses ON DELETE SET NULL — if the replied-to comment is deleted, the reply remains, just becomes top-level. Whereas postId uses ON DELETE CASCADE — if the article is deleted, all comments under it are deleted. Different scenarios, different cascade strategies, don't apply one size fits all.

---

Step 5: Many-to-many — articles and tags, using a junction table

An article can have multiple tags, and a tag can have multiple articles. This is a many-to-many relationship, use a junction table post_tag:

CREATE TABLE `post_tag` (
  `postId` int(11) NOT NULL,
  `tagId` int(11) NOT NULL,
  PRIMARY KEY(`postId`, `tagId`),  -- composite primary key, same space-saving logic as before
  KEY `tagId` (`tagId`),
  ...
)

The tag table stores tag names (unique constraint), post_tag only stores associations. This is the same design pattern as the likes table — many-to-many relationship → junction table + composite primary key.

---

Understand the relationships of the 6 tables in one diagram

Draw the entire blog's table relationships as an ASCII diagram:

                 ┌─────────┐        ┌─────────┐
                 │   user  │        │   tag   │
                 │ id,name │        │   id    │
                 └────┬────┘        └────┬────┘
                      │                  │
        ┌─────────────┼─────────┐        │
        │             │         │        │
   ┌────┴────┐   ┌────┴────┐    │   ┌────┴────┐
   │  avatar │   │  post   │    │   │post_tag │  many-to-many
   │ (FK)    │   │(author FK)│   │   │(junction)│
   └─────────┘   └────┬────┘    │   └─────────┘
       1:1            │         │
                      │         │
              ┌───────┴───┐     │
              │   comment │     │   comments on comments
              │(postId FK)│     │   (parentId self-ref)
              └───────────┘     │
                      │         │
                 ┌────┴────┐    │
                 │user_like│◄───┘    composite primary key
                 │  _post  │        (userId, postId)
                 └─────────┘

Design pattern summary:

Relationship type	Modeling method	Example in this system
One-to-many (1:N)	Add foreign key on the "many" side	user→articles, user→avatar
One-to-many (self-referencing)	Add parentId foreign key in own table	comment→comment
Many-to-many (M:N)	Junction table + composite primary key	articles↔tags, users↔articles(likes)

---

About indexes, a unified understanding to add

After reading the above, index decisions can actually converge into one main thread:

Index creation decision chain
    │
    ├─ Primary key is always an index (clustered index)
    │
    ├─ High-frequency query fields → create index
    │     Example: postId is queried/referenced by foreign key → KEY postId
    │
    ├─ Composite index → leftmost column already covered → don't create duplicate
    │     Example: composite primary key (userId, postId) covers userId queries
    │
    └─ Unique constraint (UNIQUE) is itself an index
          Example: user.name → UNIQUE KEY name

This isn't "memorizing rules," but first figuring out what the business's high-frequency queries are, then deciding which indexes to create. More indexes are not always better; each additional index adds write overhead and disk usage.

---

Conclusion

Back to that friend's opening question: Why split into 6 tables?

Because table granularity = business entity granularity. Entities become their own tables, relationships become their own tables. This isn't just "standardized and pretty," it's for fast queries, easy scaling, good sharding — all external constraints (foreign keys, composite primary keys, cascade strategies) are derived backwards from "what relationship does this table carry."

And what this article really wants to leave in your mind is this sentence:

To decide whether a field needs a separate index, first check if it's the "leftmost column of some composite index." Split tables by entity, create indexes by leftmost — two "looks," that's the core judgment of table design.

Next time you design a table, first ask yourself: "Is this table an entity, or a relationship?" Different answers, completely different modeling.

Leave you an open question: Using parentId self-referencing for unlimited comment levels saves space, but querying "all descendants of a certain comment" will be more troublesome. What alternative solutions can you think of? Share your approach in the comments.