Ask the Expert

Managing survey systems

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:

1.Yes
2.No
3.Others(Specify)

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?

    Requires Free Membership to View

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.

This was first published in January 2006

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: