[thelist] Survey answers schema

[Hershel Robinson]

Bill Moseley wrote:
> Another database question.  For a survey I have a table that holds
> answers.  But a question can generate different types of answers --
> such as free text, a enum list of options( "red", "blue"), or an intger (1..10).


> I'm not very fond of the multi-columns.  Is there a better method?

Interesting question. I can't say it's better, but one other way to set 
it up would be to have three tables for answers, one for each type. Each 
answer has an id. Let's say you call them answers_free, answers_ten, 
answers_select

Then you have a table of responses, each with an id. I am assuming a 
'response' means the answers given by one survey participant.

Then you have three more tables, each with three fields:

response_id
question_id
answer_id

You have one of these for each answers table, thus 
responses_answers_free, responses_answers_ten and responses_answers_select.

Now each 'answers' table can have the correct data type for it's answer 
field. You will simply query each of the responses_answers table to get 
a listing of all the answers any particular response generated.

Hershel

-- 
Gallery Robinson Web Services
http://web.galleryrobinson.com/