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:
- Create a person table with the details of the person taking the survey.
- Create a questionnaire table that identifies the questionnaire, but not the questions.
- 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.
- 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.
- 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.