title: Database design for a survey
body: <p>I need to create a survey where answers are stored in a database. I'm just wondering what would be the best way to implement this in the database, specifically the tables required. The survey contains different types of questions. For example: text fields for comments, multiple choice questions, and possibly questions that could contain more than one answer (i.e. check all that apply).</p>
  
  <p>I've come up with two possible solutions:</p>
  
  <ol>
  <li><p>Create a giant table which contains
  the answers for each survey
  submission. Each column would
  correspond to an answer from the
  survey. i.e. SurveyID, Answer1,
  Answer2, Answer3</p>
  
  <p>I don't think this is the best way
  since there are a lot of questions
  in this survey and doesn't seem very
  flexible if the survey is to change.</p></li>
  <li><p>The other thing I thought of was
  creating a Question table and Answer
  table. The question table would
  contain all the questions for the
  survey. The answer table would contain
  individual answers from the survey,
  each row linked to a question.</p>
  
  <p>A simple example:</p>
  
  <p><strong>tblSurvey</strong>: SurveyID</p>
  
  <p><strong>tblQuestion</strong>: QuestionID, <em>SurveyID</em>, QuestionType, Question  </p>
  
  <p><strong>tblAnswer</strong>: AnswerID, <em>UserID</em>, <em>QuestionID</em>, Answer</p>
  
  <p><strong>tblUser</strong>: UserID, UserName</p>
  
  <p>My problem with this is that there
  could be tons of answers which would
  make the Answer table pretty huge.
  I'm not sure that's so great when it
  comes to performance.</p></li>
  </ol>
  
  <p>I'd appreciate any ideas and suggestions.</p>