[iddimu]

Hi all -

I am trying to write some code to handle a survey. I need some advice on how to layout my tables/handle the survey answers.

I would have no problem with it if they were all multiple choice / yes-no type questions...my problem (and serious confusion) comes in when there is this:

10.  Can you say why you didn't use any medication?
	 -  Didn't want to
	 -  Didn't try
	 -  Medical Reasons
	 -  Pregnancy
	 -  Financial Reasons
	 -  Difficult to obtain
	 -  Ins. doesn't cover
	 -  Other _____________________

The possible selections in the above question are all checkboxes - so one to many can be selected. I could probably figure out how to handle that alright, but I'm getting thrown off by the "Other" selection.

Would I need to use a separate table for every question that has an "Other" option? That would seem kind of strange, but I'm no database expert by any means

What about this:

tblSurvey
PK - SurveyID
FK - ClientID

tblQuestions
PK - QuestionID
FK - SurveyID
Question

tblAnswers
PK - AnswerID
FK - QuestionID
Answer

???

Does that make sense? If you need more clarification, please ask...

Thanks!

This post has been edited by iddimu: 20 September 2008 - 12:22 PM

[Martyr2]

Yup, that is exactly what you need to do. That model will also work for things like drop down boxes, radio buttons, and any controls which have more than one value.

Your responses (which I hope you are also storing in the database) will follow a similar pattern. However I am also experimenting with a way for responses to be stored as an XML format given the idea that your survey may eventually drop/edit/add questions as time goes on and I want people's responses to the survey to reflect the state of the survey at the time they answered.

But you are definitely on the right track with that thinking and you will find it very flexible.

[iddimu]

Thanks for your quick response, Martyr2!

Haven't had much time to work on this piece since I originally posted. I will update once I have a solution.

Your idea of storing the responses in XML is intriguing. The client didn't seem to think that their survey questions were going to change, and as such the pay I'm getting is representing that fact! So, I'm not overly concerned with the state of the survey at the time it was administered and don't want to over-engineer for what I'm getting paid...my main concern is how to keep the comments in the textbox associated to the 'Other' checkbox. I'll save the change in survey questions for another paycheck .

Thanks again!