MySQL table structure for reporting
Follow my question yesterday ....
MySQL table construction for the questionnaire
I sat down with my boss last night to check out how I propose to create a database. However, now I am more confused than ever.
He has been using Access for years and asked if I would be able to generate reports from only one column for an answer (ENUM). He feels, from his experience with access, that every possible answer (i.e. very satisfied, fairly Satified, justly dissatisfied, very dissatisfied) should have its own column and numerical value (i.e. 100, 66.6, 33 ,thirty).
This means that the database can generate reports that show average satisfaction nationally and for each individual retailer.
I would really appreciate some responsibility as I really don't want to be wrong.
thank
source to share
In this case, I would not rename it, I would go for the "rating" column. So the columns can be:
userid, questionid, score 1,1,4 1,2,4 1,3,3 2,1,1 2,2,4 ...
1 is very dissatisfied and 4 is very satisfied.
Then a query like:
select 25*avg(score) from Blah
will give you the total percentage.
select 25*avg(score), questionid from Blah group by questionid
will give you% for the question.
Access is not really a database, so don't listen to your boss;)
source to share
JD is correct considering the need for averaging over the count, 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 will generate a report with the design we are talking about: write a little script to generate some fake data (i.e. just randomly generate a lot of value in the range 1..4), and then use some SQL (JD has good starting points) to generate some Q&D reports.
source to share
Just in case anyone else has this problem; I also found a tutorial which is quite helpful. http://www.roughguidetophp.com/generating-reports-using-mysqls-aggregate-functions-sum-max-min-and-more/
He clarifies what JD and Genehack said.
source to share