3

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

ERD model of Database

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!

Eric S
  • 1,336
  • 15
  • 20
  • Do you want to track each individual rate? or just an average per post? – Eric S Mar 24 '16 at 17:29
  • I'd of thought to get an average rating per a post I would need to record each individual vote on a specified post? –  Mar 24 '16 at 17:32
  • 1
    You should track the ratings on a per post basis. This allows you more flexibility in the long run as well as being in NF – VikingBlooded Mar 24 '16 at 17:38

3 Answers3

3

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.

Alon Eitan
  • 11,997
  • 8
  • 49
  • 58
Big_Al_Tx
  • 954
  • 9
  • 14
1

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.

Eric S
  • 1,336
  • 15
  • 20
  • On a side note, your `UserDetails` table should have a primary key of `UserDetailsID` and not `Username` to be a normalized database. See: http://sqlmag.com/database-administration/sql-design-how-choose-primary-key – Eric S Mar 24 '16 at 17:53
  • SO just to double check...The RatingScore would be the field which has values 1-5 stars? and then on the app, it will display the overall average of all the rating scores of a specific PostID, I presume is based on a separate SQL function? –  Mar 24 '16 at 17:57
  • @ChristianBryant Yes, `RatingScore` would have the value the specific User rated the Post. Then, you could either create a Stored Proc(https://dev.mysql.com/doc/connector-net/en/connector-net-tutorials-stored-procedures.html) to retrieve the average or do the logic inside your Android App code. – Eric S Mar 24 '16 at 18:02
  • Thankyou very much, been very helpful! –  Mar 24 '16 at 18:03
0

you can use stretcher like this 

enter code here
        $table->bigIncrements('id');
        $table->bigInteger('user_id')->unsigned();
        $table->ipAddress('ip')->nullable();
        $table->integer('star');
        $table->string('type');           
        $table->string('title');
        $table->string('average')->nullable()->default(0);
        $table->integer('count_star')->default(0)->nullable();
        $table->float('star_avg')->default(0)->nullable();
        $table->float('unstar_avg')->default(0)->nullable();
Amirex
  • 241
  • 3
  • 9