How to implement a star rating system in my database design?

advertisements

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!


It depends on how you want to store the Rating.

If you want to track each individual rating. You will need a Rating table with the following:

Column
---------
RatingID (PK)
Username (FK) (UserDetailsID if you change the primary key of the UserDetails table)
PostID (FK)
RatingScore
DateRated

In the app, you would then pull back every RankScore with that PostID and do the calculation based off a count of said PostID.

I would recommend the above method because it will allow you do more things with the Ratings in the future. Plus, you have a good structure to follow in place with your Comments table. ex. Show who Rated what and at what score.

If you want to attach it to the Post table, another table is not needed. You would simply add Rating_Count and Average_Rating to the table. In your app, you would than have to perform an update every time the Post is rated. You would have to pull the Rating_Count and Average_Rating, increment the Rating_Count by one, recalculate the Average_Rating and perform the update.

My second suggestion is less flexible if you ever want to enhance your Rating setup though.