Q:

Best table design for storing stats results?

I'm developing a websolution which amongst other must store the statistic results about the use of various products in a mysql database. With limited mysql database design experience and equally limited experience about statistics I'm wondering whether my options for the table design below are making sense or whether there are better options? I looked at related topics on this site (like Storing And Displaying Stats ) but could not find something that answers my question.
At this development stage I dont know yet what all specific stat results I want to store in the future, so I need a flexible design.
Simplified solution 1 for table structure (e.g. table fields):
             field1  field2                     field3       field4                 field5             fieldn 
              id      stattype                   statresults  nrofpeopleperhousehold  incomeperhh     created
value example: 1      boughtproductsperyearperhh   4           3                           $33000    2010-02-19

Simplified solution 2 for table structure (e.g. table fields):
field1  field2     field3       field4
id      stattype   statresults  created

All statresults for a particulat stattype (such as 'average_number_of_bough_products_per household') are within field3.
Fields 4 and 5  of solution 1 contain the search criteria on which the user can find particular stats results for a given stattype. This design looks simple and clear to me but is not very future proof as more possible criteria may be needed in the future.
Solution 2 is my my opinion more future proof as the search criteria (such as fields 3 and 4 of solution 1) are embedded with field 3 (statresults) (using some coding like json), but with the consequence that the statresults field will contain much more data...

A:

The second form is definitely more scalable.
I would suggest that you include a "formula" column that can contain the formula you want to use for the statistic.
Do note, however, that you will have code that creates the statistics running somewhere.  It will be quite difficult for that code to use the formula column (basically, it would require dynamic SQL).  Somewhere you are likely to have a case statement like:
select (case when stattype = 'average_number_of_bough_products_per_household'
             then nrofpeopleperhousehold / incomeperhh -- or whatever
        . . . 
        end)

If users need to search on the statistics affected by one column value, you can either search through the formula column or have another table with a list of the fields defining each statistic.

