Best way to create a survey database

[AdRock]

I have to create a database for a survey and i am stumped on the database structure.

 

The survey consists of sections and in each section is a number of questions

 

The questions can consist of:

 

Likert Scale

Multiple Choice

Select Choice

Yes/No

Text

 

I have created a polls database before which consists of poll questions in one table and poll answers in trhe other table so presume this would be similar.

 

What is bugging me is there are different types of answers to questions such as a text answer or yes/no.  What is the best way to store each answer becuase a text answer is longer than a straight yes/no in terms of field size.  Would it be best to have a text field for every answer or have a varchar with length of 255 which limits the user to a fairly short answer.

 

Has anyone ever had to do something like this before and what would you suggest for a database structure

 

This is my table for structure for poll questions

CREATE TABLE `pollquestions` (

  `poll_id` int(11) NOT NULL auto_increment,

  `question` varchar(100) NOT NULL default '',

  `current` enum('0','1') NOT NULL default '0',

  `answer1` varchar(50) NOT NULL default '',

  `answer2` varchar(50) NOT NULL default '',

  `answer3` varchar(50) NOT NULL default '',

  `answer4` varchar(50) NOT NULL default '',

  `answer5` varchar(50) NOT NULL default '',

  PRIMARY KEY  (`poll_id`),

  UNIQUE KEY `id` (`poll_id`)

)

 

This is my tablke structure for poll answers

CREATE TABLE `pollanswers` (

  `answer_id` int(11) NOT NULL auto_increment,

  `poll_id` int(11) NOT NULL default '0',

  `answer` varchar(50) NOT NULL default '',

  `visitorIP` varchar(15) NOT NULL default '',

  PRIMARY KEY  (`answer_id`),

  UNIQUE KEY `Id` (`answer_id`)

)

[fenway]

You'll need a table of answers too... don't limit it to 5.