Scalable Database Design for Likes, Comments, and Bookmarks in Mobile Apps

1. Scenario Requirements

Mobile apps need to support basic social interactions such as displaying the total number of likes, checking whether a specific user has liked a post, showing a user's personal like list, and listing all users who liked a particular article.

2. MySQL Solution

For projects with data volumes below ten million rows, a traditional MySQL design with sharding and caching is sufficient and offers strong stability.

-- Post table
create table post {
    post_id int(11) NOT NULL AUTO_INCREMENT,
    ...
    star_num int(11) COMMENT 'like count'
}

-- User table
create table user {
    user_id int(11) NOT NULL AUTO_INCREMENT,
    ...
    star_num int(11) COMMENT 'like count'
}

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

Common queries: select post_id from star where user_id=? – retrieve posts liked by a user. select user_id from star where post_id=? – retrieve users who liked a post.

Likes can be aggregated periodically and stored back into the post and user tables. This design works well for modest data sizes but faces scaling problems when a single table becomes a bottleneck; sharding and redundant tables increase storage and maintenance overhead.

2.1 Redis Solution

When the like count reaches hundreds of millions, MySQL read/write pressure grows, and a cache or NoSQL store becomes necessary. Redis is commonly chosen because of its rich data types and high performance.

Redis can be used in two ways: as a pure cache in front of MySQL, or as a storage layer combined with MySQL for persistence.

Scenario A – Display Like Count

# Set initial count for article 888
SET star:tid:888 898
# Increment count atomically
INCR star:tid:888

Scenario B – De‑duplication (Prevent Duplicate Likes)

Store a set of user IDs that have liked a specific article.

# Add user IDs to the set for article 888
SADD star:list:tid:888 123 456 789
# Check if a user has liked the article
SISMEMBER star:list:tid:888 456

Scenario C – User's Like List

The same set from Scenario B can be queried to build a user's personal like list.

Scenario D – Article's Like List

Retrieve the full set of user IDs for a given article using the same Redis set.

Redis also supports storing the like count as a string, hash, or sorted set. Using a hash reduces the number of global keys and saves memory, but adds routing complexity (e.g., mapping article IDs to hash slots) and may require additional logic to locate a user ID within a hash or set.

3. Data Consistency

When Redis is used as a storage layer, persistence must be enabled (both RDB snapshots and AOF logs) to avoid data loss, which consumes a significant portion of server memory and requires careful capacity monitoring and scaling.

Any data duplication between MySQL and Redis introduces consistency challenges; developers must design synchronization mechanisms to keep both stores in sync.