Database Design for General Web Form

Question

I want to create the back end of a general web form containing questions that the user can answer. There are several types of questions.

• "Normal" text question: simple text text answers the question. Example : general personal information, such as name.
• Textarea question: the same, but with more space to write an answer. Example : field "More information you want to add."
• Multiple choice question: A question with some predefined answers from which you can choose one. An example . How good can you program PHP? [] not very good [] average [] I wrote a book.
• ... (additional types of questions should be supplements without excessive hacking)

The entries that users enter must be stored in a MySQL database.

The problem is that the form must be editable. It will not be edited very often, but when it changes, the changes will not be affected by existing materials. Because of this, I do not think that it is enough to make a configuration through an XML file.

My approach is as follows:

• Questions are configured using the MySQL database (best practices?)
• The layout of the database is as follows:
  • the 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 is no longer used and stored only in the database for compatibility with old views.
  • q_multiplechoice
    • id: INT
    • questionid: INT
    • Answer: TEXT
  • Arguments
    • id: INT
    • userid: INT
  • submissiondetails
    • submit: 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 best approach to use here and you want you to ask if you can give me some tips on what design changes I should apply.

(Another approach that I was thinking about was to store the HTML string with the rendered feed in the departures database and use a simple configuration file to configure questions. The old views are not affected by the configuration changes since they were already saved in MySql. Problem with this approach is when design changes and presentation should be displayed in another design.)

Answer 1

First of all, let me agree to veto the idea of ​​storing HTML in a database, with the possible exception of a few tags, such as line breaks, bold, emphasis and underline, only for the question text. Defining surveys and using their results will be much easier if you focus on the text / semantics of the survey rather than View .

To support View ("layout"), the CSS configuration may be a ticket. This will include the identifier of the question, which will be used as the identifier for the div (or other html container) where View decides to save the question. multiple class names can also be specified in the question entry, but defined in CSS.

There seem to be important things in the proposed database schema. However, I do not see where the responses received are stored; is that in table view + submingsdetails? If so, where will the MULTIPLE response type be stored, are they converted to text in the dataanswer? (I don’t think they should, unless we chose to display slightly different values ​​when the poll was changed during the campaign.)

A few missing attributes and ideas:

• MULTIPLE (or another type) should be able to support the choice of the type of "radio button" ("only one"). a possible way to do this is to add an attribute to the type in order to determine the maximum number of allowed options, the general poll thing: "select 3 from the following ..."
• The question record can be a “campaign” or SurveyID , which allows you to store multiple surveys in one repository.
• Without hesitation, some questions may be related to the previous question such as "Boolean" . (If the person interviewed answered “no” to own a car, do not ask about the oil change frequency ...) This can be determined using the identifier questionID and ResponseValue (? Text for general?).
• Question table: Add a “Page Number ” to group questions (if this information is not defined in the concept of the question container, such as “survey” or “campaign”).
• Question table: add a ' Sequential number ' that allows you to ask questions in a predetermined order (if this is not from the survey / campaign table not shown here)
• q_multiplechoice: (or any table that lists the parameters for this MULTIPLE CHOICE, I have doubts about this table, seeing its fields as showin in the question.) add a Sequence Number to list the parameters in a specific order.