Designing Like/Comment/Favorite Features: MySQL and Redis Schemas with Consistency Considerations

Likes, comments, and favorites are basic features in app development; this article discusses their database design requirements and presents practical solutions.

Requirements: display like count, check if a user has liked (deduplication), show personal like list, and show article like list.

MySQL solution: use separate tables for posts, users, and likes. Example DDL statements:

-- 文章表
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:

select post_id from star where user_id=? // posts liked by a user

select user_id from star where post_id=? // users who liked a post

This design works for data under tens of millions but faces scaling problems when the table grows, requiring sharding or redundant tables.

Redis solution: for large‑scale data, combine Redis with MySQL. Redis can act as cache or primary storage. Example commands for different scenarios:

Scenario a – store like count as a string:

// set initial count
127.0.0.1:6379[2]> set star:tid:888 898
OK
// increment atomically
127.0.0.1:6379[2]> incr star:tid:888
(integer) 899

Scenario b – deduplication using a set:

// add user id to article's like set
127.0.0.1:6379[2]> sadd star:list:tid:888 123 456 789
(integer) 3
// check if user has liked
127.0.0.1:6379[2]> sismember star:list:tid:888 456
(integer) 1

Other structures such as hash or sorted set can reduce global keys but introduce routing complexity; the choice depends on specific needs.

When using Redis as storage, enable both RDB and AOF for persistence, monitor memory usage, and be aware of consistency issues that arise from data copies.

The article concludes that there is no universal solution; developers must select the appropriate design based on traffic volume, consistency requirements, and resource constraints.