Building a survey

Databases
1

So I’ve been charged with creating an online satisfaction survey (previously only done on paper) and My first question has already come up, its about table design. Right now I have:


CREATE TABLE survey_answers (
  id_answer int(11) unsigned NOT NULL auto_increment,
  answer_question int(11) unsigned NOT NULL,
  answer_value varchar(25) NOT NULL,
  PRIMARY KEY  (id_answer),
  KEY answer_question (answer_question)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

CREATE TABLE survey_questions (
  id_question mediumint(5) unsigned NOT NULL,
  question text NOT NULL,
  question_type tinyint(1) unsigned NOT NULL,
  question_order smallint(3) unsigned NOT NULL,
  PRIMARY KEY  (id_question)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

CREATE TABLE survey_responses (
  user_id mediumint(5) unsigned NOT NULL default '0',
  question_id mediumint(5) unsigned NOT NULL,
  answer_id mediumint(5) unsigned NOT NULL,
  KEY user_id (user_id,question_id,answer_id)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

CREATE TABLE survey_users (
  id_user int(11) unsigned NOT NULL auto_increment,
  user_fname varchar(25) NOT NULL,
  user_lname varchar(25) NOT NULL,
  user_email varchar(50) NOT NULL,
  user_phone_day int(10) NOT NULL,
  user_phone_opt int(10) NOT NULL,
  user_addy_street varchar(75) NOT NULL,
  user_addy_city varchar(25) NOT NULL,
  user_addy_zip mediumint(5) unsigned NOT NULL,
  user_agency varchar(50) NOT NULL,
  PRIMARY KEY  (id_user)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

I’m wondering if this is a good table structure or if you have any recommendations that would be better?

Also a couple questions are based off a started question, such that you may not need to answer based off a question IE: Have you done X in the past 6 months? yes/no if yes answer the next 5 questions if no skip them. I don’t know how to incorporate this idea.

I know how to hard code this idea into my php, however, if I’m going to hard code things in like that I might as well forget storing the questions in a database. The idea is if we change our survey next time we send it out it wont require editing code, just changing/removing/adding entries in the database.

edit: I think I shoulda put this in the MySQL forum, if a mod could move please. Sorry.
Done

2

  1. What is the link between the answers and questions table? And the answers and responses table? id_answer and answer_question in the answers table are int(11), while in the other two tables they seem to be mediumint(5).

  2. Are all the questions multiple choice? If so, you can add a column in the answers table where you store the question id of the next question to be asked when given that answer.
    If you’d want to have more complex logic, like skipping questions based on the answers of more than 1 previous question, then this solution won’t work. But for a ‘based on 1 answer’ logic I think it’ll work just fine.

3

Thanks guido, I didn’t even notice that my indexes were different sizes. The answers table is possible answers for each question, and the responses table matches a question with an answer with a user. Someone had suggested using row for all of a respondents answers however, I feel this would limit my ability to say count all the answers from question 13.

I will try out adding the column for next question in the answers table and see if I can’t work with it like that.