0

I am building a mobile Android application where users are able to find the nearest locations around them and then when viewing these places, they are allowed to see the details and also rate it. On top of this, they are allowed to add new places onto the map. I want to be able to lower the chance of people adding fake spots the best I can and so this is why I want to implement a ratings system, which is another layer security for fake spots.

I am not sure how I should design my database tables. Right now I only have:

Location: name, address, type, terrain, difficulty, lng, lat, rating.

The ratings column is only allowed values: 1, 2, 3, 4, 5.

What else would I need to keep the sum of the ratings?

After designing the tables. I want to know the most appropriate way to calculate the average ratings and then be able to delete them based on their ratings. The problem is, if a spot has 1 vote with a rating of 1 which is 100% negative then it will be considered bad when a a spot has a vote of 100 with 90 votes with 1 and 10 votes with 5. Surely, the first one should be considered worse and then deleted. How do I counter this?

Johnathan Au
  • 5,244
  • 18
  • 70
  • 128

2 Answers2

0

There's lots of ways of doing this. More complex trend-monitoring systems buffer votes and look at the rate over the last day, hour, minute etc.

However, for a simple system, you could look at total number of votes and average rating. You may not care about the sum (and you can get it anyway from the other two).

To update the rating:

average = ((average*total)+new)/(sum+1);
total ++;

When you look at deleting them you'll want to set a threshold for a minimum number of votes (total) and then delete based on average rating. 1 bad vote probably isn't really enough, how many you think is, depends on your usage levels.

For other answers: PHP/MySQL - algorithm for "Top Rated"

Community
  • 1
  • 1
Philip Whitehouse
  • 4,293
  • 3
  • 23
  • 36
0

I would say making a separate table containing foreign keys of the id of the location and id of the user submitting the review would be useful(as well as there rating of course). This way users could edit their review at a later time.

Brent Hronik
  • 2,357
  • 1
  • 27
  • 43