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.
thanks!