How would I implement a star rating system into my Database design?

Question

I am currently making an Android app where I want to allow users to rate posts, posted by other users.

This is my current design, however, would I include Rating as a separate table or included in the Media upload table?

If it is a separate table would it be something like this?

Rating table: (Rating, PostID, Rating_Count, Average_Rating)

If anything else looks wrong with my design that would also be appreciated!

Answer 1

It all depends on how you want the ratings to work, who gets to up-vote or down-vote, whether you track everyone that votes to keep from having multiple votes from the same person, etc.

This is what I would do: add a unique numeric (long?) RatingID field for each vote, link the PostID field to the Comments table, add a Rating (integer) field that is limited to values from 0 to 5 (or whatever range you prefer), and then calculate the average from all votes cast (delete the Rating_Count and Average_Rating fields). You could define a view to calculate the average ratings for each post to use for your routine for determining how to display it next to the Post.

Also, I would use an ID for each user, not use their names.

So my table would look like this:

RatingID, PostID, UserID, Rating

Also, to keep users from voting multiple times, the table would not allow multiple entries for the same PostID and UserID.