The Six Database Patterns That Make Every Blog's Backend Tick
theme: github
In the previous article, we already designed the three most basic tables for a blog system:
user
avatar
post
And we got to know:
PRIMARY KEY
UNIQUE
INDEX
FOREIGN KEY
one-to-many
But what really makes database design interesting are the following features:
Users like posts
Users bookmark posts
Users comment on posts
Posts have multiple tags
Users upload post images
These features introduce several very important database design patterns:
many-to-many
junction table
composite primary key
composite index
self-referencing
cascading delete
SET NULL
In this article, we will connect them all together.
1. Starting with "Likes"
The requirement is very simple:
Users can like posts.
First, observe the business relationship.
One user can like:
Post A
Post B
Post C
And one post can also be liked by:
User 1
User 2
User 3
Therefore:
User → multiple posts
Post → multiple users
This is called:
A many-to-many relationship.
2. Why does many-to-many need a junction table?
Suppose we try to record directly in the user table:
likedPostIds
It might become:
1,5,8,10,20
This kind of design makes querying, constraints, and updates very troublesome.
Conversely, recording in the post table:
likedUserIds
has the same problem.
In relational databases, many-to-many relationships are usually resolved through:
A junction table.
So we create:
user_like_post
3. The likes table is actually a "relationship table"
For example:
user_like_post
userId postId
1 100
1 200
2 100
It does not represent three "like objects".
Instead, it represents three relationships:
User 1 liked Post 100
User 1 liked Post 200
User 2 liked Post 100
Therefore, this kind of junction table usually does not necessarily need an extra:
id
because:
userId + postId
itself is sufficient to uniquely identify a like relationship.
4. How to build the likes table?
It can be designed like this:
CREATE TABLE `user_like_post` (
`userId` INT NOT NULL,
`postId` INT NOT NULL,
PRIMARY KEY (`userId`, `postId`),
KEY `idx_postId` (`postId`),
CONSTRAINT `fk_like_user`
FOREIGN KEY (`userId`)
REFERENCES `user` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_like_post`
FOREIGN KEY (`postId`)
REFERENCES `post` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
5. Why is the primary key two fields?
Here appears:
PRIMARY KEY (`userId`, `postId`)
This is called:
A composite primary key.
It is not a single field that is unique, but the combination:
userId + postId
is unique.
For example:
userId postId
1 100
1 200
2 100
is valid.
But:
1 100
1 100
is invalid.
This exactly corresponds to a business rule:
The same user cannot like the same post repeatedly.
So the database structure itself helps us guarantee business correctness.
This is a very typical design principle:
Rules that can be enforced by database constraints should not rely solely on business code to maintain.
6. A composite primary key is also a composite index
In MySQL:
PRIMARY KEY (`userId`, `postId`)
is not only a unique constraint, but also creates an index.
The order of this index can be understood as:
(userId, postId)
Therefore, it is very suitable for queries like:
SELECT *
FROM user_like_post
WHERE userId = 1;
Meaning:
Which posts has User 1 liked?
It is also suitable for:
SELECT *
FROM user_like_post
WHERE userId = 1
AND postId = 100;
Meaning:
Has User 1 liked Post 100?
7. Why is a separate index on userId unnecessary?
Because the composite index:
(userId, postId)
already exists.
It starts with:
userId
Therefore:
WHERE userId = ?
can already utilize it.
If we create another:
KEY `userId` (`userId`)
it would be a duplicate index in many cases.
Duplicate indexes will:
Occupy more disk space
Maintain an extra index on insert
Maintain an extra index on update
Maintain an extra index on delete
So more indexes are not always better.
8. Why does postId need a separate index?
Consider another query:
SELECT *
FROM user_like_post
WHERE postId = 100;
Meaning:
Which users liked Post 100?
Although we have:
(userId, postId)
it is primarily organized by:
userId
If we query only by:
postId
it cannot effectively use the prefix of this composite index.
So we create:
KEY `idx_postId` (`postId`)
This is actually a very good example for understanding composite indexes.
You can first remember:
A composite index
(A, B)is generally suitable for queries starting with A, but you cannot simply assume it is equivalent to having two independent indexes onAandB.
This is related to the commonly mentioned:
Leftmost prefix principle.
9. Indexes are not built just because "a field is important"
Through the likes table, you should start to build a true indexing mindset.
It is not:
userId is important
→ build index
postId is important
→ build index
But rather:
How does the business commonly query?
Can existing indexes cover it?
Is an additional index really needed?
For example:
PRIMARY KEY(userId, postId)
already supports:
WHERE userId = ?
WHERE userId = ? AND postId = ?
still frequently needs:
WHERE postId = ?
so add:
INDEX(postId)
This is real index design.
10. You already know how to build the bookmarks table
Now add:
Users bookmark posts.
The relationship is exactly the same:
User
many-to-many
Post
So it is another junction table:
user_collect_post
For example:
CREATE TABLE `user_collect_post` (
`userId` INT NOT NULL,
`postId` INT NOT NULL,
PRIMARY KEY (`userId`, `postId`),
KEY `idx_postId` (`postId`),
CONSTRAINT `fk_collect_user`
FOREIGN KEY (`userId`)
REFERENCES `user` (`id`)
ON DELETE CASCADE,
CONSTRAINT `fk_collect_post`
FOREIGN KEY (`postId`)
REFERENCES `post` (`id`)
ON DELETE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
You will find:
Likes
Bookmarks
have extremely similar database structures.
In the future, when you encounter:
Students selecting courses
Users joining group chats
Users following topics
Actors starring in movies
Users participating in events
you should develop a sensitivity to:
Many-to-many + junction table.
11. Next, design the comments table
Comments are slightly more complex than likes.
A comment at least needs:
id
content
postId
userId
That is:
Who
Under which post
Commented what
For example:
id 1
content Well written
postId 100
userId 7
Translated into plain language:
User 7 posted "Well written" under Post 100.
12. Why does a comment also need parentId?
Real blogs usually support:
Comment
└── Reply
└── Reply to reply
For example:
Comment 1:
This article is well written
Comment 2:
Thank you!
Comment 3:
It really explains clearly
If Comment 2 is a reply to Comment 1, it can be saved as:
comment
id content parentId
1 This article is well written NULL
2 Thank you 1
parentId = 1 means:
My parent comment is Comment 1.
13. A table can actually relate to itself
Here:
comment.parentId
points to:
comment.id
So the foreign key is:
FOREIGN KEY (`parentId`)
REFERENCES `comment` (`id`)
This is called:
Self-referencing.
A foreign key does not necessarily have to point to another table.
A table can absolutely point to itself.
This design is often used for:
Comment replies
Menu trees
Department hierarchies
Category trees
Folder structures
14. Complete comment table design
For example:
CREATE TABLE `comment` (
`id` INT NOT NULL AUTO_INCREMENT,
`content` LONGTEXT NOT NULL,
`postId` INT NOT NULL,
`userId` INT NOT NULL,
`parentId` INT DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_postId` (`postId`),
KEY `idx_userId` (`userId`),
KEY `idx_parentId` (`parentId`),
CONSTRAINT `fk_comment_user`
FOREIGN KEY (`userId`)
REFERENCES `user` (`id`),
CONSTRAINT `fk_comment_post`
FOREIGN KEY (`postId`)
REFERENCES `post` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_comment_parent`
FOREIGN KEY (`parentId`)
REFERENCES `comment` (`id`)
ON DELETE SET NULL
ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
15. What does ON DELETE CASCADE mean?
Notice the post foreign key:
ON DELETE CASCADE
Suppose:
Post 100
has:
100 likes
30 bookmarks
50 comments
Now Post 100 is deleted.
These relationships are no longer meaningful:
user_like_post.postId = 100
comment.postId = 100
So:
ON DELETE CASCADE
means:
When the parent record is deleted, the data that depends on it is also deleted.
For example:
Delete post 100
Automatically delete
→ its like relationships
→ its bookmark relationships
→ its comments
This is:
Cascading delete.
16. What does ON DELETE SET NULL mean?
But the comment reply relationship is slightly different.
For example:
Comment 1
└── Comment 2
If Comment 1 is deleted:
Should Comment 2 also be deleted?
This depends on the business.
If we want:
Comment 2 to remain, just that the original parent comment no longer exists.
We can use:
ON DELETE SET NULL
So after deleting Comment 1:
Comment 2.parentId
changes from:
1
to:
NULL
So a simple understanding:
CASCADE
Delete along with it
SET NULL
Keep the data, but remove the relationship
17. Why does Tag need a junction table again?
Posts may also have tags:
JavaScript
React
MySQL
Database
Backend
One post can have many tags:
Post A
→ JavaScript
→ React
→ Frontend
One tag can also belong to many posts:
JavaScript
→ Post A
→ Post B
→ Post C
So:
post
many-to-many
tag
is another set of many-to-many relationships.
Therefore:
tag
post_tag
two tables appear.
18. The tag table
CREATE TABLE `tag` (
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
Why does:
name
need UNIQUE?
Because you generally don't want:
JavaScript
JavaScript
JavaScript
to appear three times in the tag table.
19. The post_tag table
CREATE TABLE `post_tag` (
`postId` INT NOT NULL,
`tagId` INT NOT NULL,
PRIMARY KEY (`postId`, `tagId`),
KEY `idx_tagId` (`tagId`),
CONSTRAINT `fk_post_tag_post`
FOREIGN KEY (`postId`)
REFERENCES `post` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT `fk_post_tag_tag`
FOREIGN KEY (`tagId`)
REFERENCES `tag` (`id`)
ON DELETE CASCADE
ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
Does it look familiar?
It is almost the same pattern as:
user_like_post
This is also a very important thing in learning database design:
Don't just memorize specific tables, discover the universal structures behind them.
20. Finally, the file table
Blog posts usually upload:
Cover images
Post images
Attachments
Real files are generally not all stuffed into MySQL.
A more common structure:
File body
→ OSS / S3 / File server
File info
→ MySQL
So the database can save:
originalname
mimetype
filename
size
width
height
metadata
userId
postId
21. Designing the file table
For example:
CREATE TABLE `file` (
`id` INT NOT NULL AUTO_INCREMENT,
`originalname` VARCHAR(255) NOT NULL,
`mimetype` VARCHAR(255) NOT NULL,
`filename` VARCHAR(255) NOT NULL,
`size` INT NOT NULL,
`postId` INT DEFAULT NULL,
`userId` INT NOT NULL,
`width` SMALLINT DEFAULT NULL,
`height` SMALLINT DEFAULT NULL,
`metadata` JSON DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_postId` (`postId`),
KEY `idx_userId` (`userId`),
CONSTRAINT `fk_file_user`
FOREIGN KEY (`userId`)
REFERENCES `user` (`id`),
CONSTRAINT `fk_file_post`
FOREIGN KEY (`postId`)
REFERENCES `post` (`id`)
ON DELETE SET NULL
ON UPDATE CASCADE
) ENGINE=InnoDB
DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_unicode_ci;
Where:
userId
means:
Who uploaded this file?
And:
postId
means:
Which post does this file currently belong to?
If the post hasn't been published yet:
postId = NULL
is also reasonable.
22. The entire database relationship finally appears
Now the blog database can be understood as:
user
│
├── avatar
│
├── post
│ │
│ ├── comment
│ ├── file
│ ├── user_like_post
│ ├── user_collect_post
│ └── post_tag
│
└── comment
tag
└── post_tag
Classify by relationship:
user → post
one-to-many
user → comment
one-to-many
post → comment
one-to-many
user ↔ post
many-to-many
via user_like_post
user ↔ post
many-to-many
via user_collect_post
post ↔ tag
many-to-many
via post_tag
comment → comment
self-referencing
via parentId
This relationship diagram is more important than memorizing all the SQL.
23. Why is there a database/blog.sql in the project?
After completing the table design, a very common project structure is:
project
│
├── src
├── ...
└── database
└── blog.sql
blog.sql can save:
CREATE TABLE
Foreign keys
Indexes
Initialization data
This way, when a new environment gets the project, it can quickly initialize the database structure through the SQL file.
You can also prepare some test data, for example:
A few users
A few posts
A few tags
Some comments
Some likes
convenient for direct use when developing APIs.
24. What you should most remember from this article is not the SQL
What you should really form are the following patterns.
Pattern 1: One-to-many
One user
Many posts
Usually:
post.userId
Pattern 2: Many-to-many
User
Many posts
Post
Many users
Junction table:
user_like_post
Pattern 3: Composite primary key
(userId, postId)
Expresses:
This set of relationships cannot be duplicated.
Pattern 4: Self-referencing
comment.parentId
→ comment.id
Expresses a tree structure.
Pattern 5: Foreign key deletion strategies
CASCADE
Parent record deleted, child records deleted along with it
SET NULL
Parent record deleted, child records remain, relationship cleared
Pattern 6: Queries determine indexes
Don't blindly add indexes just because you see a field.
First ask:
What is the most common WHERE clause?
What is the most common JOIN?
Can an existing composite index be utilized?
Summary
Database design is not:
Casually creating a table for every business feature.
The real process should be:
Analyze entities
↓
Analyze entity relationships
↓
Decide one-to-many or many-to-many
↓
Design primary keys
↓
Design foreign keys
↓
Turn business rules into constraints
↓
Design indexes based on query requirements
When you understand this set, you will see:
Likes
Bookmarks
Follows
Tags
Comments
Replies
and no longer think of them as six completely different problems.
You will find that they are actually just a few database design patterns repeating over and over.
In the next article, we will pull the perspective up from the database:
For a truly live website, starting from a user typing
juejin.cn, how exactly does the request pass through DNS, Nginx, server clusters, OSS, and CDN?