CODEWITHSUNDEEP
databasealgorithm
PranshuKhandal
PranshuKhandal

Reputation: 879

How to store feedback like stars or votes of users with efficiency?

I am making a system similar to our Play Store's star rating system, where a product or entity is given ratings and reviews by multiple users and for each entity, the average rating is displayed.

But the problem is, whether i should store the ratings in database of each entity with a list of users who rated it and rating given, but it will make it hard for a user to check which entities he has rated, as we need to check every entity for user's existence,

Or, should i store each entity with rating in user database but it will make rendering of entity harder

So, is there a simple and efficient way in which it can be done

Or is storing same data in both databases efficient, also i found one example of this system in stackoverflow, when the store up and down votes of a question, and give +5 for up vote while - for down vote to the asking user, which means they definitely need to store each up and down vote in question database, but when user opens the question, he can see his vote, therefore it is stored in user's database

Thanx for help

Upvotes: 1

Views: 597

Answers (2)

GolezTrol
GolezTrol

Reputation: 116140

I would indeed store the 'raw' version at least, so have a big table that stores the productid/entityid, userid and rating. You can query from that table directly to get any kind of result you want. Based on that you can also calculate (or re-calculate) projections if you want, so its a safe bet to store this as the source of truth.

You can start out with a simple aggregate query, as long as that is fast enough, but to optimize it, you can make projections of the data in a different format, for instance the average review score per product. This van be achieved using (materialized) views, or you can just store the aggregated rating separately whenever a vote is cast.

Updating that projected aggregate can be very lightweight as well, because you can store the average rating for an entity, together with the number of votes. So when you update the rating, you can do: 

NewAverage = (AverageRating * NumberOfRatings + NewRating) / (NumberOfRatings + 1)

After that, you store the new average and increment number of ratings. So there is no need to do a full aggregation again whenever somebody casts a vote, and you got the additional benefit of tracking the number of votes too, which is often displayed as well on websites.

Upvotes: 3

Okeme Christian
Okeme Christian

Reputation: 312

The easiest way to achieve this is by creating a review table that holds the user and product. so your database should look like this.

product
--id
--name
--price

user
--id
-- firstname
--lastname

review
--id
--userId
--productId
--vote

then if you want to get all review for a product by a user then you can just query the review table. hope this solves your problem?

Upvotes: 1

Related Questions