CODEWITHSUNDEEP
phpandroidmysql
Polar Bear
Polar Bear

Reputation: 131

how to construct a better Ratings Table in mySQL? is it appropriate like this?

I am new to mySQL, and I need help on constructing a table. Here are the conditions:

  1. Each user is allowed to rate only once at per restaurant (second rating onward will be update of the previous record).

  2. Average = (AverageQuality + AverageService + AverageClealiness)/ NumofRates

  3. AverageQuality = all user's (userRateQuality) / number of users rate..

  4. AverageService = all user's (userRateService) / number of users rate..

The following is my table:

CREATE TABLE IF NOT EXISTS `ratings` (
    `Average` int(5) NOT NULL,
    `AverageQuality` int(5) NOT NULL,
    `AverageClealiness` int(5) NOT NULL,
    `AverageService` int(5) NOT NULL,
    `NumofTotalRates` int(11) NOT NULL,
    `userID` varchar(20) NOT NULL,
    `restaurantID` int(11) NOT NULL,
    `userRateQuality` int(5) NOT NULL,
    `userRateService` int(5) NOT NULL,
    `userRateClealiness` int(5) NOT NULL
)

I know it is weird but I have been bothered by this question for a few days, and I do not know how to fix this schema or construct a better one. I need some suggestions/guidance. Please reply or ask any question as it might helps to improve my table. Thanks!

Upvotes: 2

Views: 832

Answers (1)

Robbert
Robbert

Reputation: 6592

Your table structure doesn't really make sense. You should store each user's rating for quality, cleanliness and service. So you could do something like

CREATE TABLE IF NOT EXISTS ratings (
   userID varchar(20) NOT NULL, 
   restaurantID int(11) NOT NULL, 
   userRateQuality int(5) NOT NULL, 
   userRateService int(5) NOT NULL, 
   userRateClealiness int(5) NOT NULL )
)

Once you have this data, you can run queries to get the average ratings. You wouldn't want to store average ratings within the same table that stores a user's ratings because the average changes each time someone adds a new rating. To get the average, you can use the MySQL AVG function. http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

Upvotes: 5

Related Questions