General discussion


Large survey database organization

By britton ·
I'm currently putting together a PHP/Mysql database for a fairly large survey (n=roughly 1000). Given that the survey is a large one (almost 200 questions) each person needs to be able to be able to save and restore their survey at any time.

As it stands, I've got multiple tables set up to organize things. There's a table for the questions on the survey (question, question type, possible answers), and another table for the users (username, password, various info) but I'm having trouble deciding where to fit in each individual user's responses. I've considered adding a whole bunch of columns to the users table that would hold their answers, but that feels really inefficient to me. The thought of having a table with 200+ columns is pretty daunting. Plus it makes for a huge pain if (when!) any revisions need to be made...

Does anybody have any experience with this type of project? While I'm sure my current design will work fine, I can't help but wonder whether there are more efficient ways of going about it.


This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Comments

Collapse -

add a table !

by WilliamSanders In reply to Large survey database org ...

column1 -> primary key of table
column2 -> foreign key of user table
column2-203 - user's answers to the questions.

hth [Bill]

Collapse -

Use rows instead of columns

by eugene.kelly In reply to Large survey database org ...

The only other way to do it is to have a user,question table. This would have columns user_id, question_id, answer_value. This assumes that all answer values are representable by the same type (varchar, number or whatever). Otherwise you need a table for each answer type. This general approach is more flexible than the 200 column approach in that it is easily changed to accommodate more or less questions (you do not have to change any table definitions.).

Related Discussions

Related Forums