Backend · MySQL

Deriving a Blog Database from Business Rules, Not Syntax

By 东风破_ · Aug 27, 2026

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

Many developers memorize CREATE TABLE syntax but freeze when facing a blank schema for a real project. This derivation-first approach — asking four questions per table and letting query patterns dictate indexes — replaces guesswork with a repeatable method that works for any relational model.

Summary

Database design starts with the business, not with CREATE TABLE syntax. This walkthrough derives three core tables — user, avatar, and post — by repeatedly asking what a table stores, what one row represents, how to uniquely find it, and how it relates to other tables. Each design decision (PRIMARY KEY, UNIQUE, FOREIGN KEY, INDEX) is traced back to a concrete query or integrity requirement: id as a stable identifier, username uniqueness for both constraint and login performance, userId with a foreign key to guarantee referential integrity, and an index on userId because the system frequently queries avatars and articles by owner.

The post table introduces the one-to-many pattern: store the "one" side's id on the "many" side. A user writes many articles, so post.userId points to user.id. The same reasoning applies to categories, departments, and orders. Constraints are reframed as business rules written into the database — NOT NULL, UNIQUE, FOREIGN KEY — that prevent bad data from entering even when application code misses a check.

The real takeaway is the derivation habit itself. When you can start from a business requirement and reason your way to fields, primary keys, indexes, and foreign keys without memorizing syntax, SQL becomes a tool rather than a barrier.

Takeaways

— Before designing any table, answer four questions: what it stores, what one row represents, how to uniquely find a row, and its relationships to other tables.

— Use an auto-increment integer id as the primary key so user identity survives username changes and URLs like /user/12 remain stable.

— A UNIQUE constraint on username serves double duty: it prevents duplicate registrations at the database level and creates an index that speeds up login lookups.

— Indexes trade space for query speed; design them based on actual query patterns (login by username, find avatar by userId) rather than guessing which fields feel important.

— Store password hashes (bcrypt, Argon2), never plaintext; the database column is really password_hash, and login verifies the hash, not a raw comparison.

— Keep image files in object storage (S3, OSS, COS) and store only metadata — filename, mimetype, size, userId, url — in the database.

— A FOREIGN KEY constraint (avatar.userId → user.id) guarantees referential integrity: the database rejects an avatar row that references a nonexistent user.

— Add an index on foreign key columns; InnoDB needs it for efficient constraint enforcement, and queries like "find avatar by userId" hit it directly.

— Model one-to-many relationships by placing the "one" side's id on the "many" side — post.userId points to user.id, and the same pattern fits categories, departments, and orders.

— Constraints are business rules written into the schema: PRIMARY KEY enforces unique identification, UNIQUE prevents duplicates, NOT NULL requires a value, FOREIGN KEY ensures valid references.

Conclusions

The article reframes database constraints not as technical overhead but as a second layer of business-logic enforcement that catches mistakes the application code misses.

UNIQUE is presented as a dual-purpose mechanism — constraint plus index — which is a mental model many tutorials skip, leaving developers to add redundant indexes later.

The one-to-many explanation avoids abstract ERD notation and instead uses a concrete rule: store the parent's id on the child row. This makes the pattern instantly transferable to any domain.

By splitting avatar into its own table, the article teaches foreign keys and indexing in a context where the design choice is debatable (small projects could inline avatar_url), making the pedagogical intent transparent rather than dogmatic.

Concepts & terms

Primary Key

A column (or set of columns) that uniquely identifies each row in a table. MySQL automatically indexes it, making lookups by primary key very fast. Cannot contain duplicates or NULLs.

UNIQUE constraint

A rule that prevents duplicate values in a column. In MySQL, it also creates an index, so it simultaneously enforces a business rule and accelerates queries that filter on that column.

Foreign Key

A column that references the primary key of another table, enforced by a FOREIGN KEY constraint. It guarantees referential integrity — the database rejects rows that point to nonexistent records.

One-to-many relationship

A relationship where one record in table A can be associated with many records in table B, but each record in B belongs to exactly one record in A. Implemented by storing A's primary key as a column in B.

Index

A data structure that trades extra storage space for faster query performance. Rather than scanning every row, the database uses the index to locate matching rows quickly. Should be designed based on actual query patterns, not perceived field importance.

Source: juejin.cn ↗ Google Translate ↗ Backup ↗