Database design for general web form

Question

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.

• "Normal" text question: the question is answered via a simple text field. Example: General personal info, like the name.
• Textarea question: the same, but with more space to write the answer. Example: A "More information that you want to add" box.
• Multiple choice question: a question with some predefined answers, from which a single one can be chosen. Example: How well can you program PHP? [ ] not very well [ ] average [ ] I've wrote the book.
• ... (additional question types should be addable without having to hack too much)

The inputs that the users enter should be stored in a MySQL database.

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.

My approach is the following:

• The configuration of the questions is made using the MySQL database (better methods?)
• The database layout is as follows:
  • questions
    • id : INT
    • question : TEXT
    • type : ENUM ('NORMAL', 'TEXTAREA', 'MULTIPLE')
    • 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.
  • q_multiplechoice
    • id : INT
    • questionid : INT
    • answer : TEXT
  • submissions
    • id : INT
    • userid : INT
  • submissiondetails
    • submissionid : INT
    • questionid : INT
    • givenanswer : TEXT

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.

(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.)

Answer 1

First off, let's agree to veto the idea of storing HTML in the database, except, maybe a few tags such as line-breaks, bold, emphasis and underline, for the question's text only. Defining the surveys and exploiting their output will be so much easier if the focus is on the text/semantics of the survey, rather than View details.

To support View ("layout") configuration CSS could be the ticket. This would key on the ID of the question which would be used as ID to the div (or other html container) where the View decides to store the question. a few class names could also be listed in the question record, but defined in CSS.

On the proposed database schema, the important stuff seems to be there. I don't see however where the submitted responses get stored; is that in submission + submitiondetails tables? If so, where do the MULTIPLE response type get stored, are they converted to text, into givenanswer? (I don't think they should, unless we prefer capturing slightly different values when the survey was modified during the campaign.)

A few missing attributes and ideas:

• MULTIPLE (or another type) should be made able to support "radio-button" type choices ("only-one-of"). a possible way to do this is to add an attribute to the type, to define the max number of choices allowed, a common survey thing: "pick 3 among the following..."
• The question record could have a "campaign" or SurveyID, allowing to store multiple surveys in the same store.
• Without geting too fancy, some questions could be predicated to a previous "boolean" type question. (If polled person respond "no" to own a car, do not ask about the frequency of oil changes...) This could be defined by a questionID and a ResponseValue (?text for genericity?).
• question table: add a 'Page Number', allowing to group the questions (unless this info is defined in a question container concept such as "survey" or "campaign")
• question table: add 'Sequence Number' allowing to get questions in a predefined order (unless such comes from a Survey/campaign table not shown here)
• q_multiplechoice: (or whatever table is where the options for a given MULTIPLE CHOICE are listed; I'm having doubt about this table seeing its fields as showin in the question.) add a Sequence Number, allowing to list the options in a particular order.