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
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
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
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.