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?
By submitting your email address, you agree to receive emails regarding relevant topic offers from TechTarget and its partners. You can withdraw your consent at any time. Contact TechTarget at 275 Grove Street, Newton, MA.
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.
Dig Deeper on Linux management and configuration
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.