title: Database design for general web form
body: <p>I want to build a back end of a general web form which contains questions which the user can answer. There are multiple types of questions.</p>
  
  <ul>
  <li>"Normal" text question: the question is answered via a simple text field. <strong>Example</strong>: General personal info, like the name.</li>
  <li>Textarea question: the same, but with more space to write the answer. <strong>Example</strong>: A "More information that you want to add" box.</li>
  <li>Multiple choice question: a question with some predefined answers, from which a single one can be chosen. <strong>Example</strong>: How well can you program PHP? [ ] not very well [ ] average [ ] I've wrote the book.</li>
  <li>... (additional question types should be addable without having to hack too much)</li>
  </ul>
  
  <p>The inputs that the users enter should be stored in a MySQL database.</p>
  
  <p>The issue I have is that the form should be editable. It won't be edited very often but when it changes, existing submissions shouldn't be affected by the changes. Due to this fact, I don't think that it is sufficient to make the config via a XML file.</p>
  
  <p>My approach is the following:</p>
  
  <ul>
  <li>The configuration of the questions is made using the MySQL database (better methods?)</li>
  <li>The database layout is as follows:
  
  <ul>
  <li>questions
  
  <ul>
  <li>id : INT</li>
  <li>question : TEXT</li>
  <li>type : ENUM ('NORMAL', 'TEXTAREA', 'MULTIPLE')</li>
  <li>active : BOOL - true if the question is used in the current form. false if it isn't used anymore and is only kept in the database for compatibility with old submissions.</li>
  </ul></li>
  <li>q_multiplechoice
  
  <ul>
  <li>id : INT</li>
  <li>questionid : INT</li>
  <li>answer : TEXT</li>
  </ul></li>
  <li>submissions
  
  <ul>
  <li>id : INT</li>
  <li>userid : INT</li>
  </ul></li>
  <li>submissiondetails
  
  <ul>
  <li>submissionid : INT</li>
  <li>questionid : INT</li>
  <li>givenanswer : TEXT</li>
  </ul></li>
  </ul></li>
  </ul>
  
  <p>As you can see, it uses four tables for a simple web form. I don't think that this is the optimal approach to use here and want you to ask if you could give me some hints on what design changes I should apply.</p>
  
  <p>(other approach I've thought about is to store a HTML string with a rendered submission in the submissions database and to use a simple config file to configure the questions. the old submissions won't be affected by config changes since they were already stored rendered in MySql. Problem with this approach is, when the design changes and the submission should be displayed in another design.)</p>