I'm using mysql and php to build some surveying software have run into something that I fear is a problem. The way I have it set it, there is a list of questions (500+ of them) that can be used in a survey. Not every survey will use all of the questions.
I have a table for all of the questions, and a table for all of the results. When I add a new question to the questions table, I have my php script add a new column to the results table.
So when somebody takes a survey, it adds a row to the results table, and for every question that got asked, it adds a result to that column. So I end up with a very long row that contains all the question results, even for the questions that were not asked (it just puts in nulls for them).
My problem is the fact that we have 500 questions now and growing. We could have as many as 2000 in the future. I can't think of a bigger mess than having a mysql table that has 2000 columns. Is it as big of a problem as I assume it is?
Can anyone think of a better way that I can store the results? Some other, more efficient table configuration? Keep in mind that it is very important that I know what question was asked so that we can properly analyze the results in the future. That means there needs to be some sort of link between the question # and the answer that it puts with the results.
I KNOW there has to be a better solution, I just can't think of it.
- Doug
I have a table for all of the questions, and a table for all of the results. When I add a new question to the questions table, I have my php script add a new column to the results table.
So when somebody takes a survey, it adds a row to the results table, and for every question that got asked, it adds a result to that column. So I end up with a very long row that contains all the question results, even for the questions that were not asked (it just puts in nulls for them).
My problem is the fact that we have 500 questions now and growing. We could have as many as 2000 in the future. I can't think of a bigger mess than having a mysql table that has 2000 columns. Is it as big of a problem as I assume it is?
Can anyone think of a better way that I can store the results? Some other, more efficient table configuration? Keep in mind that it is very important that I know what question was asked so that we can properly analyze the results in the future. That means there needs to be some sort of link between the question # and the answer that it puts with the results.
I KNOW there has to be a better solution, I just can't think of it.
- Doug