is number of columns a factor?

Anon

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

[deleted]

Indeed there is a better solution, called "normalization"

Your results table should look like this:
UserID | questionID | answer

This way you can simply add one row to the table for each answer. The record would contain the ID of who answered the question, the ID of the question that was answered and ofcourse the answer itself.

You can use a simple join statement to get a list of all questions answered by a person, including the answers

Anon

Wait a minute. I understand how to join two tables to get the columns for each table, but how do you use join rows? That is what you meant right? join rows?

Thanks, by the way. I think that really is a better solution.

Doug

kparker

To fill it out a bit:

    / Table to hold all the questions /
    create table question
    (
    id int auto_increment,
    q text,
    ...
   &nbsp😉;

    create table user
    (
    id int auto_increment,
    name text,
    ...
   &nbsp😉;

    create table response
    (
    user_id int, / references user.id /
    quest_id int, / references question.id /
    answer text
   &nbsp😉;

Now, to get all the questions answered by a given user:

select user.id, user.name, question.q, response.answer from user, question, answer where response.user_id = user.id and question.id = response.quest_id;

DISCLAIMER: this is all off the top of my head, so beware of typos and thinkos!