I'm setting up a very small DB to be used for customer based product reviews. There are two tables:
product_tbl: id int unsigned auto_increment reviews int unsigned // number of times product reviewed product varchar(45)
review_tbl: id unsigned int product_id int unsigned // id from product_tbl price int quality int features int email varchar(45) pros longtext cons longtext
price, quality, features are ratings based on a scale of 1-10 - for each int (price, quality, features) I provide a clickable array of 10 dhtml/css stars that update a hidden field and that field gets stored in its relative db field).
What I want to do is query based on the highest ranked product and ranking is determined by number of average of all values in price, quality, features.
So if toasters A & B each have 'x' reviews (in this example its three):
Toaster A: Price=1,5,7 Quality=3,4,6 Features=1,1,1
Toaster B: Price=3,3,3 Quality=6,7,8 Features=5,8,2
Then Toaster B would be the higer rated because:
Toaster A = (4, 4, 1)/3 = 3
Toaster B = (3, 7, 5)/3 = 5
How's the best way to construct this query in SQL knowing that I may have 100's of toasters and 10's of thousands of reviews. Some toasters (top 5 brands) may get 80% of the reviews while the remaining 20% of the reviews may be spread across the remaining toasters.
I would appreciate any suggestions!