Rating System Database Structure

I have two entity groups. Restaurants and Users. Restaurants can be rated (1-5) by users. An开发者_如何学Pythond rating fromeach user should be retrievable.

Resturant(id, name, ..... , total_number_of_votes, total_voting_points ) User (id, name ...... )

Rating (id, restaurant_id, user_id, rating_value)

Do i need to store the avg value so that it need not be calculated every time ? which table is the best place to store avg_rating, total_no_of_votes, total_voting_points ?

Well, if you store the average value somewhere; it will only be accurate as of the last time you calculated it. (i.e. you have 5 reviews; then store the averages somewhere. You get 5 more new reviews, and then your saved average is incorrect).

My opinion is that this sort of logic is perfectly suited to a middle-tier. Calculating an average shouldn't be very resource intensive, and really shouldn't impact performance.

If you really want to store it in the database; I would probably store them in their own table, and update those values via triggers. However, this could be even more resource intensive than calculating it in the middle-tier.

Some database, for example, PostGreSQL, allow you to store an array as part of a row. e.g.

create table restaurants (
    ...,
    ratings integer[],
    ...
);

So you could, for example, keep the last 5 ratings in the same row as the restaurant. When you get a new rating, shuffle the old ratings left, and add the new rating at the end, then calculate the average.