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