MySQL Table structure to produce reports

Question

To follow on from my question yesterday....

http://stackoverflow.com/questions/323842/mysql-table-design-for-a-questionnaire

I sat down with my boss yesterday afternoon to run through how I was proposing to design the database. However, now I am more confused than ever.

He has been using Access for many years, and has questioned whether I will be able to produce reports from only using one column for the answer (ENUM). He feels from his experience with Access that each possible response (i.e. Very Satisfied, Fairly Satified, Fairly Unsatisfied, Very Unsatisfied), should have it's own column and numerical value(i.e. 100, 66.6, 33.3, 0).

This is so that the database can produce reports that show the average satisfaction nationally and for each retailer individually.

I would really appreciate some guidence, as I really don't want to get this wrong?

Thank you

Answer 1

In this case I wouldn't go for an enum, I'd go for a "score" column. So the columns might be:

userid, questionid, score
1,1,4
1,2,4
1,3,3
2,1,1
2,2,4
...

1 being very unsatisfied and 4 being very satisfied.

Then a query like:

select 25*avg(score) from Blah

will give you your overall percentage.

select 25*avg(score), questionid from Blah group by questionid

will give you a % per question.

Access isn't really a database, so don't listen to your boss ;)

Answer 2

JD is right, given the need to do score averaging, the INT column is the way to go.

As for your boss, well, he's wrong. 8^) Perhaps the best way to convince him is to demonstrate how you'd do the report generation with the design we're talking about: write a little script to generate some fake data (i.e., just randomly generate a lot of values in the 1..4 range) and then use some SQL (JD has some good starting points) to generate some Q&D reports.

Answer 3

Just in case anyone else has this problem; I have also found a tutorial which is quite useful. http://www.roughguidetophp.com/generating-reports-using-mysqls-aggregate-functions-sum-max-min-and-more/

It clarifies what JD and Genehack have said.