Database table layout for questionnaire

Need advice

[eggy ricardo]

Hiya guys,

I need some advice on designing a database for an online questionnaire i am creating. Basically i was thinking of either:

Having the fields:
userID ¦ Question1 ¦ Question2 ¦ Question3 etc
and have a row for each person who fills out the questionnaire

or:
userID ¦ Question number ¦ Answer
and having a row for each answer that each person gives

What do you think would be the best approach for this in terms of performance.

Cheers
Richard

[ironik]

The first option isn't going to allow you to add or remove questions very easily, your basically stuck with a set number of questions. The second option is much better, so you'd have a table structure like this:

QUESTIONS
question_id
question

ANSWERS
answer_id
question_id
user_id
answer

USERS
user_id
username...

[lovethecoast]

Horizontal verses vertical table structures each have their benefits and drawbacks. In this case, if your questionnaire is going to have a rating system, and you want to draw aggregate data and graph it or show it in a meaningful manner, you're either going to have to go with a horizonal structure or write a lot of code to extract the data and recompile it in a format you can pass off to whatever it is you want to display.

Horizonal is certainly much, much faster, but if you're not 100% certain you won't be changing the questionnaire often, it's best to go vertical. What I do in these cases normally is try and go horizontal with "room to grow" -- add a few extra columns up front that I can use later and write extraction code to use them if they exist and ignore them if they don't. That way I don't have to go back and re-write code over and over.

S