Designing Database Schemas for Likes, Comments, and Favorites in App Development

Likes, comments, and favorites are basic features in app development. This article examines the functional requirements—displaying like counts, deduplication, personal like lists, and article like lists—and explores database design solutions.

MySQL solution : For projects with data under ten million records, a sharded MySQL table combined with cache can handle the load. Example schemas for post, user, and star tables are provided.

-- 文章表
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 include retrieving posts liked by a user ( select post_id from star where user_id=?) and users who liked a post ( select user_id from star where post_id=?). The design works for moderate data volumes but faces scaling issues when a single table becomes a bottleneck, requiring sharding or redundant tables, which adds storage and maintenance overhead.

Redis solution : When data reaches billions, Redis is used either as a cache or as primary storage alongside MySQL. Two usage patterns are described: storage and pure cache. The article details how to store like counts, deduplicate likes, and maintain like lists using strings, hashes, sets, and sorted sets.

//以文章id=888为例 
127.0.0.1:6379[2]> set star:tid:888 898 //设置点赞数量 
OK 
127.0.0.1:6379[2]> incr star:tid:888 //实现数量自增 (integer) 
899

//以文章id=888为例 
127.0.0.1:6379[2]> sadd star:list:tid:888 123 456 789 //点赞uid列表 (integer) 
3 
127.0.0.1:6379[2]> sismember star:list:tid:888 456 //判断是否点赞 (integer) 
1

Scenarios a–d illustrate storing like counts (scenario a), preventing duplicate likes (scenario b), retrieving a user's liked items (scenario c), and listing likes for an article (scenario d). The article discusses trade‑offs of different Redis data structures, such as using strings versus hashes versus sorted sets, and emphasizes the need for persistence (RDB + AOF) to ensure data consistency.

Finally, the article notes that there is no one‑size‑fits‑all solution; engineers must choose based on traffic, consistency requirements, and operational constraints, and encourages readers to share their own approaches.