1

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

Community
  • 1
  • 1
cevizmx
  • 361
  • 1
  • 7
  • 24

1 Answers1

2

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.

Community
  • 1
  • 1
jhaagsma
  • 2,316
  • 2
  • 19
  • 25
  • thanks for your review. I would not use it neither but cannot change the code or the structure.. – cevizmx Oct 21 '16 at 22:52
  • Well; if you're looking for *alternate* ways to display quick stats; yea, make a stats table, and run the above stats queries and store them every time a new record is inserted, and just reference that. You could certainly do an incremental update for mean, but I don't think you can calculate the median incrementally. – jhaagsma Oct 21 '16 at 23:00