mySQL help
 

hi,

i know a bit about mySQL as i use it in my PHP pages pretty effectively.

What i can't get my head around though is how to structure a database for average scores? Say if i want to run a 'Poll' that collates an average customer rating for a particular company (there will be multiple companies). Do i need to store the results for each company in a separate table? Then work out the averages on the fly by doing a simple average calculation and displaying the result on the page?

I'm not sure how you could keep a running total - as averages (as far as my maths tells me) are based on all the results added together and then divided by the total instances...

I'm really unsure how to structure this in SQL so that it is efficient....

This might be a bit confusing but I wasn't sure how to explain it...

Thanks :)

you wouldn't store this information, you would run it on the fly. I am hopeing you have a structure something like:

tblCustomer:
intCustomerID
strCustomerName
.....

tblCompany:
intCompanyID
strCompanyName
.....

tblRating:
intCustomerID
intCompanyID
intRating

In order to get the average rating per company:
SELECT AVG(intRating), DISTINCT(intCompanyID) FROM tblRating
- or -
SELECT AVG(intRating), intCompanyID FROM tblRating GROUP BY intCompanyID

Note: SQL may differ slightly for mySQL


HTH :)

That's how I'd do it too. You could denormalise it a bit if you never cared about individual ratings...

tblCompany:
intCustomerID
strCompanyName
intCumulativeRating
intRatingN

Then
SELECT intCompanyID, strCompanyName, intCumulativeRating / intRatingN FROM tblCompany

Thanks guys - most helpful :)