cevizmx cevizmx - 1 month ago 12
MySQL Question

PHP questionnaire calculation design

So I need an opinion / a way of solution on the matter below.

There is this questionnaire which has 67 questions, coded with PHP and uses a database (MySQL). By design the data table is as follows, where it contains ID and question numbers.

So,

I will generate a report with these answers. i.e. I'll get the mean, median for each question and show them on a user report screen. There are 493 rows now and want to think something which will not get longer and longer to process in time.

Any opinions or an approach which makes the process easier(bearable)? Shall I create a class for the calculations and run for each questions and store the values on a view? Found an answer here for a similar issue but just could not make sure. Really would love to hear any ideas.

Sample Database table

Answer

Personally, I'd avoid using a table 67 columns wide, and do a 3-column table with a two-column Primary-key instead.

ID | Q  | Result
1  | 1  | 1
1  | 2  | 3
1  | 3  | 2
...
4  | 5  | 4

Then run stats on that; it'll be 67 times longer, but your stats will be all be primary-key lookups. And anything less than a couple million rows will be pretty damned fast anyway.

Oh, and do the stats using mysql, it's good at that sort of thing. For example:

SELECT AVG(Result) WHERE Q = 1;

And use this solution for the median.