storing survey answers of different data types

We've designed a web survey with a MySQL backend. The survey is composed of a series of questions with a possible answer of 1-5 or N/ A, in a particular order. We have a table Questions that stores the text of the question and its answer.

CREATE TABLE `Questions` (
`id` int(10) unsigned NOT NULL auto_increment,
`question` text NOT NULL,
`answer` enum('1','2','3','4','5','N/A') default NULL,
 ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;

A NULL answer value means that the respondent has not answered the question.

Now we are being asked to add survey questions that can have answers of different data types. The spec is to have the survey 'configureable', so that at any point in the future, when someone says, "We need a new survey that asks {text answer question}, {1-5 question}, {true false question}", we can do that without changing the database structure.

I'm trying to think of the best way to store those answers, but every method I come up with seems somewhat hackish.

Some questions may have a yes/no or true/false answer, some may have an integer answer ( "How many times in the past month have you used tech support?"), another answer may have a date, a string, a multiple choice with a single value, a multiple choice with multiple values, etc. Or sometimes, a particular answer value may prompt a sub-question ( "What disappointed you about...?" )

Brainstorming, the best method I could come up with is to have a different table for each answer type, but that feels susceptible to data integrity issues. In other words, I would have

CREATE TABLE `Questions` (...);
CREATE TABLE `TrueFalseAnswers` (...);
ALTER TABLE `TrueFalseAnswers`
  ADD CONSTRAINT `TrueFalseAnswers_ibfk_1` FOREIGN KEY (`question_id`) REFERENCES `Questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; CREATE TABLE `TextAnswers` (...);
ALTER TABLE `TextAnswers`
  ADD CONSTRAINT `TextAnswers_ibfk_1` FOREIGN KEY (`question_id`) REFERENCES `Questions` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

etc.

One problem with the above is that I can't guarantee that at least one and only one answer exists for any question in the DDL alone.

Another solution might be to have a binary or string column for the answer in the Questions table, and encode all answers into some string or binary format, and store them in the answer column. That gives me at least one and only one answer for every question, but then I lose access to aggregate features in SQL queries. This strikes me as not a very 'relational' solution.

So, I see problems with the two ideas outlined above. Is there a 'best' way to solve this problem? Received on Mon Apr 20 2009 - 20:20:41 CEST