Questionnaire Database Structure

[x1nick]

I have built a questionnaire system up where a user can create a questionnaire with a numerous amount of questions of different types. Now the biggest problem with this is the number of database queries I seem to be pushing out per page is just silly. And think maybe I am taking completely the wrong approach.

 

The requirements of the system:

Allow user to save questionnaire, auto marks as complete when all required fields are answered (user can save questionnaire and return later)

Have a selection of question types

• Short text entry
  
• Yes/No (also if answered yes, please enter data into short text field)
  
• Single Choice
  
• Mutiple Choice
  
• Long text entry
  

 

Did also want a scale of 1-10 but thats fairly straight forward if I ever need to add that in!

 

Current structure for defining a questionnaire

CREATE TABLE IF NOT EXISTS `question_define` (
  `questionnaireid` int(10) NOT NULL AUTO_INCREMENT,
  `title` varchar(250) NOT NULL,
  `description` text NOT NULL,
  `complete` tinyint(1) NOT NULL,
  PRIMARY KEY (`questionnaireid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

 

For defining a question, parent referes back to this table to store mutiple choice/single choice options

CREATE TABLE IF NOT EXISTS `question_item` (
  `questionid` int(10) NOT NULL AUTO_INCREMENT,
  `questionnaireid` int(10) NOT NULL,
  `order` int(5) NOT NULL,
  `typeid` int(10) NOT NULL,
  `parent` int(10) NOT NULL DEFAULT '0',
  `question` varchar(250) NOT NULL,
  `required` tinyint(1) NOT NULL,
  `requiredif` int(10) NOT NULL DEFAULT '0',
  PRIMARY KEY (`questionid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=122 ;

 

Storing a answer (userid referes to user table)

CREATE TABLE IF NOT EXISTS `question_answer` (
  `answerid` int(10) NOT NULL AUTO_INCREMENT,
  `userid` int(10) NOT NULL,
  `questionnaireid` int(10) NOT NULL,
  `answercomplete` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`answerid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

 

And storing an answer to an individual question

CREATE TABLE IF NOT EXISTS `question_answeritem` (
  `answeritemid` int(10) NOT NULL AUTO_INCREMENT,
  `questionid` int(10) NOT NULL,
  `answerid` int(10) NOT NULL,
  `answer` varchar(250) NOT NULL,
  PRIMARY KEY (`answeritemid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

 

Now I know what I have done works, Im just not 100% sure its the most efficient way of doing it.

Especially with questions which have multiple options which need to be stored in the database

 

Not too sure how the best way to pull the data out of the database in a single query either (im not brilliant at long queries  )

 

Any help would be really appreciated 

[roopurt18]

I created a similar system once upon a time.  I can't really go into the nitty gritty of it, but here are some things I came up with.

 

1)  Database tables exist to store information and allow us to query that information.  When it comes to surveys you need to store surveys, questions, and user responses.  You will need to ask questions about the responses, such as "How many people answered with 'Apple' for question #3 of survey #15?"  You will probably not need to answer questions about the surveys themselves, such as "How many surveys contain a 1-to-10 rating style question?"

 

2) Survey and question characteristics are likely to change over time.  Today you support short answer, true / false, and "check all that apply" questions.  Tomorrow you might support "rating questions."  All in all, it will be *difficult* to keep a database structure consistent with how frequently your question "types" may alter over time.

 

3) Survey responses are one of: boolean / bit, short-text, long-text.  Therefore all you really need to store a response is: question_id, user_id, bit_answer, short_answer, long_answer, other_flag

 

4) I don't recommend having a table that stores the "possible" answers linked via referential key integrity to the actual responses table.  If you do this, then it will be difficult to implement responses of type "Other."  Since you don't know what "Other" might be, how can you pre-load the "possible_answers" table so the referential keys will work out?  You can't.

 

Anyways, taking all of that together I made something along the lines of:

 

survey

id, name, description, start_tm, end_tm, survey

The `survey` field is the serialized PHP object that represents the survey.  It's pretty easy to predict that surveys will have a name, description, start date, and end date.  Therefore the table has actual columns for those values.  Beyond that, who knows what the client can ask for?  Therefore everything else about a survey, which will change drastically with the life of the application, is a serialized PHP object stored in `survey.`

 

question

id, survey_id, prompt, order, type, question

The `question` field is a serialized PHP object that represents all details of a question not contained within prompt, type and order.  The logic behind this design is the same as that with the `survey`.`survey` column.

 

response

id, user_id, create_tm, survey_id

A general record for a user's submittal of a survey

 

response_item

id, response_id, bit_answer, short_answer, long_answer, was_other

Store the user's response here.  bit_answer contains 0 for false answers, 1 for true answers.  short_answer contains any textual response that will fit within varchar(128) or however many characters you deem appropriate.  long_answer is a text field for paragraph-like responses.  was_other is a flag to indicate if the user clicked a checkbox named "other" on the survey form and typed in their own response.

 

The survey and question tables are flexible enough to allow for easy modification of the code in PHP without having to fuss with the database every time you change a property name in PHP.  All of the tables contain enough information to perform simple reporting through an interface that doesn't have access to PHP.

 

Hopefully that helps you some.