Database Reference
In-Depth Information
OK, now that you have your solution, let's take a look at ours:
CREATE TABLE video_rating (
videoid uuid,
rating_counter counter,
rating_total counter,
PRIMARY KEY (videoid)
);
Here are the rules for the ratings:
• The rating counter is incremented when someone gives any rating. We will
use the example of three stars as a rating value.
• A rating total is a cumulative record of the rating, so for the three stars rating,
you give
++3
for the counter.
• The average is calculated as
rating_total/rating_counter
.
You might have also come up with not-so-perfect solutions. Here are some of the
possibly wrong solutions:
•
Read the number and increment it
: This is an antipattern, so it reads before
performing the writes (this leads to concurrency problems in a distributed
environment).
•
Store every rating and sum them up later
: This is incorrect, because you
need to design with queries in mind, not with the data in mind. If you store
the sum, you can use it for the query. If you store each rating, you need to
calculate it for the query.
Once you have designed your table for ratings, do this exercise:
Write a SQL statement that will add a rating of three stars for a given video.
A solution for this is given here.
Note that you don't have to prefill the table with any data. Rather, you can start
using a new table for updates right away. That is the property of counters:
UPDATE video_rating SET rating_counter = rating_counter + 1,
rating_total = rating_total + 3 WHERE
videoid = 58178e0a-5ea1-4b65-8bb8-a3f610b5cf68;
Now, verify the rating by querying the table.
