How to store data from a customer rating system?

[Adrian Cre]

I would like users to be able to enter a rating on a scale of 1-9 for various given qualities of customers. For example, if a user has a particularly exasperating conversation with a customer, that user could choose to enter '9' on a scale of "exasperatingness." Another example: if a user has a long and fairly positive conversation with a customer, that user could choose to enter '6' on a scale of "likes to socialize" (Not trying to be "big brother" or anything, just trying to make it so that the new trainees can work with the easiest customers, identify customers with growing frustrations, etc.) I just need the series of numbers 1-9 entered in order so I can get the average, slope, etc.

I guess I could just create a table with a separate record for every single rating entered, along the lines of:

customer_id TINYINT(10) REFERENCES customer(customer_id),
rating_type ENUM('exasperatingness', 'likes to socialize' . . .),
rating_date DATETIME
rating_number TINYINT(1),
rating_number_id TINYINT(10) NOT NULL AUTO_INCREMENT PRIMARY KEY

But this seems somehow wasteful. It seems like this would be carefully dividing up this data, only to later join it into a chronological series. If I ever want a rating for a certain quality and customer, I'm always going to want the WHOLE SERIES of ratings in the order entered, never any one rating by itself. Is there a better way of doing it?



Edited 1 time(s). Last edit at 09/26/2007 09:32PM by Adrian Cre.