0% completed
YouTube Likes Counter: Database Schema
On This Page
Step 6: Database Schema
- UserLikes Table (Likes/Dislikes per User per Content item)
- ContentStats Table (Aggregated Likes/Dislikes per content)
Three things this schema decides
Step 6: Database Schema
Here is the NoSQL schema for tracking user like/dislike actions on posts and comments. It is optimized for high scalability and quick retrieval of the latest user actions, while minimizing query latency and ensuring efficient updates.
1. UserLikes Table (Likes/Dislikes per User per Content item)
Stores the latest like/dislike action by each user on a specific post or comment. This table is keyed by user_id and content_id (composite key) so that each user-item pair has at most one record (the most recent action).
| Field | Data Type | Description |
|---|---|---|
| user_id | String | Unique identifier of the user who performed the action. (Part of the composite primary key) |
| content_id | String | Unique identifier of the content (post or comment) that was liked/disliked. (Part of the composite primary key) |
| action_type | String (enum) | Type of action: either "like" or "dislike". Indicates the user's latest reaction on the content item. |
| timestamp | DateTime | Timestamp of the latest action by the user on this item. Used to record when the action occurred (or last changed). |
| version | Integer | Goes up by one on every change to this row. The write is conditional on the version the caller read, which is what makes a toggle safe when two requests arrive together. Step 8 covers it. |
2. ContentStats Table (Aggregated Likes/Dislikes per content)
Maintains the total count of likes and dislikes for each post or comment. This table is keyed by content_id so the like/dislike counts for any item can be fetched in a single, fast lookup. It is updated whenever a user's like/dislike on that item changes (often via an event or stream).
| Field | Data Type | Description |
|---|---|---|
| content_id | String | Unique identifier of the content (post or comment). Acts as the primary key for this table (one record per item). |
| total_likes | Integer | Cumulative count of all "like" actions for this item. Updated whenever a new like is added or a dislike is changed to a like. |
| total_dislikes | Integer | Cumulative count of all "dislike" actions for this item. Updated whenever a new dislike is added or a like is changed to a dislike. |
| updated_at | DateTime | When the aggregation last wrote this row. The recount job in Step 10 uses it to find items the pipeline has stopped updating. |
Three things this schema decides
Undo is a delete, not a third value. action_type holds like or dislike only. A user reverting to no reaction removes the row. In Cassandra a delete writes a tombstone, which is a marker saying the row is gone that is kept until compaction removes it. At this write rate, undo traffic is worth measuring, because a partition full of tombstones makes reads of that partition slow.
The totals are stored, never counted on read. Answering "how many likes does this video have?" by counting rows in UserLikes would scan every partition that holds one. ContentStats exists so the answer is a single key lookup. That is the trade this schema makes. One extra write per reaction gives a read that costs the same whether a video has ten likes or ten million.
UserLikes is the source of truth and ContentStats is derived. Every count in the system can be recomputed from the per user rows. This is what makes an eventually consistent count safe to serve, and Step 10 uses it to correct drift.
One growth note. Step 3 puts this table in the hundreds of billions of rows over five years. Partitions are keyed by user_id, so a heavy user's partition grows without limit. Two answers are common. Put a time bucket in the partition key, or move reactions older than a set age to cheaper storage. Neither is needed to serve a count, because the count lives in ContentStats.
Next: Step 7, which compares three ways to keep the count.
On This Page
Step 6: Database Schema
- UserLikes Table (Likes/Dislikes per User per Content item)
- ContentStats Table (Aggregated Likes/Dislikes per content)
Three things this schema decides