0% completed
YouTube Likes Counter: Database Schema
On This Page
Step 6: Database Schema
- UserLikes: one row per user per item
- ContentStats: the totals per item
Three things this schema decides
Step 6: Database Schema
We now design the schema that stores every like and dislike on posts and comments. A schema is a set of decisions about what each read and each write will cost. This one must do three things well. It must scale, find a user's latest action in one lookup, and keep every update cheap. We get all three from two NoSQL tables, shown in the diagram below.
1. UserLikes: one row per user per item
This table answers one question: what is this user's latest reaction to this item? It holds each user's most recent like or dislike on one video or comment. Its key is user_id and content_id together, which is called a composite key. The composite key gives each user and item pair at most one row. So a second reaction from the same user overwrites that row instead of adding a new one.
| 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 (video 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: the totals per item
The second table exists for the read path. ContentStats holds the total likes and dislikes for each video or comment, keyed by content_id alone. That single key means the counts for any item come back in one fast lookup. The row updates whenever a user's reaction on that item changes. The update usually arrives through an event or a stream.
| Field | Data Type | Description |
|---|---|---|
| content_id | String | Unique identifier of the content (video 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, so a user who removes a reaction deletes the row. In Cassandra a delete writes a tombstone, a marker saying the row is gone. The marker stays until compaction, the background cleanup, removes it.
Tombstones collect inside a partition, the slice of a table that lives together on one node. A partition full of them makes reads of that partition slow. So at this write rate, undo traffic is worth measuring.
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 instead.
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, so a wrong total is never permanent. That is what makes an eventually consistent count safe to serve. Step 10 uses this property to correct drift.
One growth note. Step 3 puts UserLikes 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.
Reading Progress
0%
On This Page
Step 6: Database Schema
- UserLikes: one row per user per item
- ContentStats: the totals per item
Three things this schema decides