Deriving a Blog Database from Business Rules, Not Syntax

When learning SQL, the truly difficult part is often not remembering how to write CREATE TABLE, but:

Faced with a real business scenario, which tables should I actually build? What fields go in each table? Why do we need primary keys, indexes, and foreign keys?

This article does not start by memorizing SQL syntax tables. Instead, it assumes we are developing a blog system similar to Juejin, and derives the database structure step by step from the business requirements.

1. Start from the business: What data does a blog system need?

Assume our blog supports these features:

User registration, login
User sets an avatar
User publishes articles
User likes articles
User bookmarks articles
User comments on articles
Articles have tags
User uploads images

Then the database might contain:

user
avatar
post
user_like_post
user_collect_post
comment
tag
post_tag
file

Don't rush to create all tables at once.

The most important skill in database design is not "writing out all tables in one breath," but:

Breaking down one business requirement at a time.

This article first tackles the three most fundamental ones:

User
Avatar
Article

2. Before designing a table, ask four questions

Whenever you see any business requirement, you can first ask:

1. What does this table store?
2. What does one row of data represent?
3. How do you uniquely find this row?
4. What is its relationship with other tables?

Once these four questions are clear, fields, primary keys, indexes, and foreign keys become much easier to determine.

3. What should the user table store?

The core data of a user system typically includes:

id
username
password

Why not stuff everything like:

avatar
slogan
birthday
address
...

into the user table right from the start?

Because the user table is usually a very high-frequency access table.

For example, login:

SELECT id, username, password
FROM user
WHERE username = ?;

Querying a user:

SELECT *
FROM user
WHERE id = ?;

These operations don't need avatar, bio, or other profile data at all.

Therefore, in some system designs, there is a tendency to:

Keep core identity data in the core user table, and split out extended profile data as needed.

Of course, this doesn't mean "avatar must absolutely be a separate table."

For a small project, you can perfectly well put avatar_url directly in the user table.

The reason for splitting out the avatar here is mainly for learning:

How to establish relationships between tables
What a foreign key is
Why indexes exist

4. Designing the user table

Let's look at a basic version:

CREATE TABLE `user` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(255) NOT NULL,
  `password` VARCHAR(255) NOT NULL,

  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

This table can be understood as:

user

id      username      password
1       zhangsan      ...
2       lisi          ...
3       wangwu        ...

One row of data represents:

A single user.

5. Why do we need an id?

id INT NOT NULL AUTO_INCREMENT

AUTO_INCREMENT means the database can automatically generate:

1
2
3
4
5
...

Then:

PRIMARY KEY (`id`)

means:

id is the unique identifier for this user record.

For example:

id = 12

No matter what the user changes their name to later, user 12 remains user 12.

So in business logic, you typically use:

/user/12

rather than directly using the username as the identity identifier for the whole system.

6. What exactly is a Primary Key?

Primary Key is:

The primary key.

The simplest understanding:

The database uses it to uniquely identify a record.

For example:

id
1
2
3

You cannot have:

1
1

because a primary key cannot be duplicated.

And MySQL will build an index for the primary key, so:

SELECT *
FROM user
WHERE id = 100;

can usually find the target user very quickly.

7. Why does username need UNIQUE?

Usernames typically cannot be duplicated.

For example, if the system already has:

zhangsan

Another person cannot register:

zhangsan

Therefore:

UNIQUE KEY `uk_username` (`username`)

is equivalent to telling the database:

username must be unique.

This way, even if the code layer forgets to check, the database itself will prevent duplicate data.

8. UNIQUE is not just a constraint, it's also an index

This point is very important.

Because during login, you are likely to execute:

SELECT *
FROM user
WHERE username = 'zhangsan';

A unique index exists on username, so the database doesn't need to search through a large number of user records one by one every time.

So UNIQUE serves two roles simultaneously:

Business constraint
Username cannot be duplicated

Query performance
Faster querying by username

9. What exactly is an index?

When first encountering databases, you don't need to dive into the details of B+ Trees immediately.

First, establish the most important intuition:

An index uses extra space to exchange for faster queries.

Suppose the database has millions of users:

SELECT *
FROM user
WHERE username = 'dfp';

If username has no suitable index, the database might need to check a vast number of records.

With an index, the database can locate the target faster.

So when designing indexes, a very important principle is:

Design indexes based on query requirements.

Not:

I feel this field is important, so I'll add an index to it.

Instead, you should think:

By which field does the system frequently query?
How does it frequently sort?
How does it frequently associate?

For example:

GET /user/:id

Frequently finds a user by:

id

The primary key already solves this.

username

The unique index solves this.

This is:

Business queries determine indexes.

10. Why can't passwords be stored in plain text?

Suppose the database directly saves:

username     password

zhangsan     123456
lisi         abc123

Once the database is leaked, all user passwords are directly exposed.

So real systems do not save the user's original password.

They usually save the result processed by a specialized password hashing algorithm, for example using:

bcrypt
Argon2

Such password hashing schemes.

Therefore, the:

password

field in the database is actually more accurately understood as:

password_hash

During login, it's also not:

Taking the original password out of the database to compare.

But rather:

Using the password hashing algorithm to verify if the password entered by the user matches.

11. Where should avatars be stored?

Many beginners, when implementing avatar upload for the first time, think:

Should I just stuff the image directly into the database?

Generally, web projects do not put ordinary image file bodies directly into relational databases.

The more common approach is:

Image file
    ↓
Object storage / Static resource server
    ↓
Get the file address

For example, it might be stored on:

Alibaba Cloud OSS
AWS S3
Tencent Cloud COS
Your own file server

The database mainly saves:

filename
mimetype
size
userId
url / objectKey

and other metadata.

12. Designing the avatar table

For example:

CREATE TABLE `avatar` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `mimetype` VARCHAR(255) NOT NULL,
  `filename` VARCHAR(255) NOT NULL,
  `size` INT NOT NULL,
  `userId` INT NOT NULL,

  PRIMARY KEY (`id`),

  KEY `idx_userId` (`userId`),

  CONSTRAINT `fk_avatar_user`
    FOREIGN KEY (`userId`)
    REFERENCES `user` (`id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

Suppose the data is:

id   filename       size     userId
1    abc.webp       10240    7

Translating this into plain language:

abc.webp is the avatar uploaded by user 7.

13. What is userId?

Here appears an extremely important concept in database design:

userId

The avatar table itself doesn't know:

Whom this avatar belongs to.

So it stores a:

userId

pointing to:

user.id

Thus:

user
id = 7

and:

avatar
userId = 7

establish a relationship.

14. What is a Foreign Key?

This segment:

FOREIGN KEY (`userId`)
REFERENCES `user` (`id`)

is the foreign key.

It can be translated as:

avatar.userId references user.id.

For example, if the user table only has:

1
2
3

Then:

avatar.userId = 2

is fine.

But if you insert:

avatar.userId = 999

and the system has no user 999 at all, the database will reject it when foreign key constraints are enabled.

So one of the important roles of a foreign key is:

Ensuring the correctness of data relationships between tables.

15. Why does userId also need an index?

In actual business, you might frequently execute:

SELECT *
FROM avatar
WHERE userId = 7;

That is:

Query the avatar of user 7.

Therefore:

KEY `idx_userId` (`userId`)

helps this query.

Additionally, in MySQL InnoDB, foreign key columns typically also need corresponding indexes to efficiently maintain associations.

16. Next, designing the article table

A blog system certainly needs articles:

post

An article at minimum needs:

id
title
content
userId

Where:

userId

means:

Who wrote this article?

17. The post table

It can be written as:

CREATE TABLE `post` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `title` VARCHAR(255) NOT NULL,
  `content` LONGTEXT,
  `userId` INT NOT NULL,

  PRIMARY KEY (`id`),

  KEY `idx_userId` (`userId`),

  CONSTRAINT `fk_post_user`
    FOREIGN KEY (`userId`)
    REFERENCES `user` (`id`)
) ENGINE=InnoDB
  DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

For example:

id    title              userId

101   JavaScript Intro    7
102   MySQL Index Study   7
103   React Hooks         12

This means:

User 7 wrote article 101
User 7 wrote article 102
User 12 wrote article 103

18. Here appears "one-to-many"

Observe:

One user
can write many articles

But:

One article
typically has only one author

So:

user 1 —— N post

This is:

A one-to-many relationship.

The implementation is very simple:

Store the "one" side's id on the "many" side.

That is:

post.userId

pointing to:

user.id

In the future, when you see:

One category has many articles
One department has many employees
One user has many orders

you should think of this design pattern.

19. Primary keys, indexes, and constraints can finally be connected

Now let's re-examine these three tables.

user

id
→ PRIMARY KEY

username
→ UNIQUE

Reason:

id uniquely identifies a user
username cannot be duplicated

avatar

id
→ PRIMARY KEY

userId
→ INDEX
→ FOREIGN KEY

Reason:

id uniquely identifies an avatar record
Frequently query avatar by userId
Avatar must belong to a real user

post

id
→ PRIMARY KEY

userId
→ INDEX
→ FOREIGN KEY

Reason:

id uniquely identifies an article
Frequently query articles of a certain user
Article must be associated with an author

You will find:

SQL syntax does not appear out of thin air.

Every design has a corresponding business reason.

20. What are database constraints really doing?

Constraints can be understood as:

Writing business rules into the database.

For example:

PRIMARY KEY
A record must be uniquely identifiable

UNIQUE
Username cannot be duplicated

NOT NULL
This field must have a value

FOREIGN KEY
Associated data must be valid

A database is not simply "storing data."

A well-designed database should also try its best to prevent incorrect data from entering.

21. What should you really master from this article?

Don't just memorize three CREATE TABLE statements.

What you should really master is this derivation process:

"User" appears
→ user table

A user record must be unique
→ PRIMARY KEY

Username cannot be duplicated
→ UNIQUE

Frequently query by username for login
→ Index

User has an avatar
→ avatar.userId

Avatar must belong to a user
→ FOREIGN KEY

One user can write multiple articles
→ post.userId
→ One-to-many

When you can derive these designs yourself based on the business, SQL has truly started to become accessible.

Summary

The first step in database design is never writing SQL, but understanding the business.

When facing a table, you can repeatedly ask:

What does this table represent?

What does one row of data represent?

What uniquely identifies it?

Which fields cannot be duplicated?

Which fields cannot be empty?

Whom does it belong to?

How does the system frequently query it?

After answering these questions, you can usually gradually derive:

Fields
Primary Key
Unique Constraints
Indexes
Foreign Keys

In the next article, we will continue designing the more interesting parts of the blog system:

Likes
Bookmarks
Comments
Tags
Files

These business requirements will truly introduce very important concepts in database design:

Many-to-many, junction tables, composite primary keys, self-referencing associations, and cascading deletes.