Storing Multiple Survey Formats and Answers

[benmanns]

I am writing a web application with a MySQL backend that will have multiple users taking various surveys. Each survey has a different number of questions; each question has two types of answers: string and enum; each enum has different numbers of values.

I want to be able to dynamically create the survey, assign questions, set question types (string/enum), assign enum variables if necessary, and save the data for each survey.

I am completely lost as to how the structure of the database should be in order to account for the different types of questions and answers. Help is greatly appreciated.

[Niheel]

Here's a quick mockup

table: survey
table: questions
table: results

 
survey
-----------
surveyid
surveytitle
surveystartdate
surveyenddate
survey_totalquestions
 
questions
-----------------
questionid
question_surveyid
question_title
question_type(enum,string)
question_string
question_enum_values (can insert array in here corresponding to each enum value)
 
results:
------------------------------------
survey_userid
survey_id
question_id
question_results
 

That's probably the quick bare bones way i'd attack it. Though you can get very complex with survey collection data. Another way to quickly learn db schema for survey applications is browse through the db schema of an opensource survey application. Try freshmeat or sourceforge to find one