Manage Learn to apply best practices and optimize your operations.

Managing survey systems

MySQL expert Mike Hillyer describes a method to managing a survey system using column specifications.

I have developed a questionnaire with text fields, radio buttons, etc using PHP and HTML. After the users that want to update enter their values into the database, one of my answers in that questionnare has two types of responses with radio button and text fields that offers answer such as:


I've created a table with the columns "User," "AnswerOfQuestion1," "AnswerOfQuestion2," ...etc., in the database. I'm faced with a problem when specifying the above described column (including the radio button & text fields). I've mostly used enum options, varchartype, medium int, for the other columns but, with regards to this question, what should I use for the column specification?

I have developed a similar system in the past using the following approach:

  1. Create a person table with the details of the person taking the survey.
  2. Create a questionnaire table that identifies the questionnaire, but not the questions.
  3. Create a question table. This contains information on the question text, the survey it is attached to, the position of the question survey and a foreign key to a table that contains potential multiple-choice answer sets. If the foreign key is null, then the question is written response.
  4. Create a table of potential multiple-choice answer sets. Rows may contain columns such as Yes/No/Maybe or Agree/Disagree/No Opinion. While this may not be fully normalized since a given response such as "Yes" may appear in multiple rows, it should strike a balance between normalization and ease of use.
  5. Create a response table. In this table you have one row per user/question. It identifies the user, the question and either the numerical or text response value, depending on whether the question was text or multiple choice (or both in the case of Other/Specify). In this case, you have a text and a numeric column for each response, allowing you to properly constrain the data.

That is from memory of my solution of several years ago, but such a normalized system should help manage a survey system.

Dig Deeper on Linux servers

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.