Backend · MySQL

The Six Database Patterns That Make Every Blog's Backend Tick

By 东风破_ · Aug 27, 2026

Read original on juejin.cn ↗ Google Translate ↗ Alt translation

Every backend engineer eventually hits the point where features like likes and tags feel like one-off puzzles. Recognizing them as the same few patterns — junction table, composite key, self-referencing FK, cascade vs. set-null — cuts design time and prevents the kind of ad-hoc schemas that crumble under real query loads.

Summary

A blog's social features — liking, bookmarking, commenting, tagging, and file attachments — are not six distinct problems. They are repeated instances of a few core relational patterns. Many-to-many relationships between users and posts (or posts and tags) demand junction tables with composite primary keys, which double as composite indexes and eliminate the need for a surrogate ID. The leftmost prefix rule then dictates when a second single-column index is warranted, and when it would just waste disk and write overhead.

Comments introduce self-referencing via a `parentId` foreign key pointing back to the same table, turning flat rows into threaded trees. Foreign key deletion policies — `ON DELETE CASCADE` for dependent child rows that become meaningless, `ON DELETE SET NULL` for replies that should outlive their parent — encode business logic directly in the schema, reducing reliance on application-level guard code.

Files split storage concerns: the binary lives in OSS or S3, while MySQL holds metadata, ownership, and an optional post association that stays NULL until the draft is published. The entire design ships as a single `blog.sql` file that seeds tables, indexes, and test data, letting any new environment bootstrap instantly.

Takeaways

— A many-to-many relationship (users ↔ posts, posts ↔ tags) is modeled with a junction table whose composite primary key `(leftId, rightId)` enforces uniqueness without a surrogate ID.

— That composite primary key also serves as a composite index, covering queries that filter by the leading column; a separate index on the trailing column is needed only when queries filter by it alone.

— Adding a single-column index on the leading column of an existing composite index is usually a duplicate that wastes space and slows writes.

— Comments use a `parentId` foreign key referencing the same table's primary key — self-referencing — to build nested reply threads.

— `ON DELETE CASCADE` on a foreign key automatically removes child rows (likes, bookmarks, comments) when a parent post is deleted.

— `ON DELETE SET NULL` on a self-referencing foreign key preserves a reply but clears its `parentId` when the parent comment is removed.

— A file table stores metadata (`originalname`, `mimetype`, `size`, dimensions, JSON metadata) while the binary stays on OSS/S3; `postId` can be NULL for files uploaded before a post is published.

— The full schema, including constraints and seed data, lives in a `database/blog.sql` file so any environment can initialize the database with one import.

Conclusions

The article treats database constraints as the first line of defense for business rules — a composite primary key prevents duplicate likes at the storage engine level, which is stronger and cheaper than checking in application code.

Index design is framed as a query-driven decision, not a per-column reflex. The explicit reasoning about when a second index on `postId` is needed (and when an index on `userId` would be redundant) is a concrete application of the leftmost prefix principle that many tutorials gloss over.

The structural similarity between `user_like_post`, `user_collect_post`, and `post_tag` is the real payload: once you internalize the junction-table pattern, features like follows, group memberships, and course enrollments become mechanical, not creative, design tasks.

Self-referencing foreign keys are introduced without drama as a standard tool for tree-shaped data — comments, menus, org charts — which counters the common instinct to reach for recursive CTEs or NoSQL for hierarchical storage.

Concepts & terms

Junction table (中间表)

A table that exists solely to link two other tables in a many-to-many relationship. It typically contains only the foreign keys from each side, often combined into a composite primary key.

Composite primary key (联合主键)

A primary key consisting of two or more columns. The combination of values must be unique, which enforces rules like 'a user cannot like the same post twice' directly in the schema.

Leftmost prefix principle (最左前缀原则)

A MySQL index rule: a composite index on `(A, B)` can serve queries that filter on `A` or on `A AND B`, but cannot efficiently serve queries that filter only on `B`. This determines when a separate index on the trailing column is necessary.

Self-referencing foreign key (自关联)

A foreign key in a table that points to the primary key of the same table. Used to model hierarchical data like comment replies, menu trees, or organizational charts.

ON DELETE CASCADE

A foreign key option that automatically deletes child rows when the parent row is deleted. Used when child data becomes meaningless without the parent (e.g., likes on a deleted post).

ON DELETE SET NULL

A foreign key option that sets the foreign key column to NULL when the parent row is deleted, preserving the child row but breaking the relationship. Used when the child should outlive the parent (e.g., a reply whose parent comment was removed).

Source: juejin.cn ↗ Google Translate ↗ Backup ↗