Database Reference
In-Depth Information
The section tag index
Let's describe just one more tagging technique. Let's say you need to place tags on
the videos. For example, you might need to find all the videos for a given tag. What
might be a wrong thing to do is to go through the
videos
table looking for this tag.
Don't iterate through all the videos looking for the tags. So, how do you do that?
Try this exercise.
How will you solve the requirement for a tag functionality? Do it yourself first and
then go back and compare it with our answer.
A solution for this is to create a
tag_index
table, as follows:
CREATE TABLE tag_index (
tag varchar,
videoid uuid,
timestamp timestamp,
PRIMARY KEY (tag, videoid)
);
The points to make a note of here are:
• We are not storing a list or a set of videos, as it will be a wrong RDBMS
pattern. Can you explain why?
• We get very fast writes.
• We get reasonably fast reads because it is a slice operation (essentially,
it is a group by operation).
Try the following exercise:
Write a query to get all the videos for a given tag. Check our GitHub repository for
the answers.
Here's one bonus exercise for you: what if you want to tag videos by popularity?
Search for the answer, and publish a good solution on the topic's forum.
