Database Design for Like, Comment, and Favorite Features in App Development

In modern app development, features such as likes, comments, and favorites are essential, and their database design must support displaying counts, checking if a user has already liked, and retrieving personal or article‑wide like lists.

1. Requirements

Show the total number of likes.

Determine whether a specific user has liked (deduplication).

Display a user's liked items, typically in a personal center.

Show the list of users who liked a particular article.

2.1 MySQL solution

-- 文章表
create table post {
    post_id int(11) NOT NULL AUTO_INCREMENT,
    ...
    star_num int(11) COMMENT '点赞数量'
}

-- 用户表
create table user {
    user_id int(11) NOT NULL AUTO_INCREMENT,
    ...
    star_num int(11) COMMENT '点赞数量'
}

-- 点赞表
create table star {
    id int(11) NOT NULL AUTO_INCREMENT,
    post_id,
    user_id,
    ...
}

Common queries:

Find articles a user liked: select post_id from star where user_id=? Find users who liked an article: select user_id from star where post_id=? Periodically aggregate the like counts back to the post and user tables.

Drawbacks of the MySQL approach

When data volume grows, a single table becomes a bottleneck; sharding is required, and maintaining two redundant tables (by post_id and by user_id) increases storage and consistency complexity.

2.2 Redis solution

When the number of likes reaches hundreds of millions, caching or using Redis as storage is necessary to offload MySQL.

Scenario a: Display like count

// Set initial count for article id 888
127.0.0.1:6379[2]> set star:tid:888 898 // set like count
OK
127.0.0.1:6379[2]> incr star:tid:888 // increment count
899

Scenario b: Deduplication (prevent duplicate likes)

Store a set of user IDs for each article, using the article ID as the key.

Scenario c: User's like list

The same set from scenario b can be queried to obtain all articles a user has liked.

Scenario d: Article's like list

// Add user IDs to the set for article 888
127.0.0.1:6379[2]> sadd star:list:tid:888 123 456 789
3
// Check if user 456 has liked the article
127.0.0.1:6379[2]> sismember star:list:tid:888 456
1

Using Redis as storage requires enabling both RDB and AOF persistence to avoid data loss, and careful capacity monitoring because the dataset occupies a large portion of memory.

3. Data consistency

When Redis is used as the primary store, persistence settings must be configured, and the system must handle the inevitable consistency challenges that arise from data replication.

In summary, for small‑to‑medium projects MySQL with sharding and caching is sufficient, while large‑scale systems benefit from a hybrid MySQL‑Redis architecture that balances durability, performance, and scalability.