phpESP 1.8 – Poll/Survey Script – DB Design

<br /> — # table of different surveys available<br /> CREATE TABLE survey (<br /> id INT UNSIGNED NOT NULL AUTO_INCREMENT,<br /> name CHAR(64) NOT NULL,<br /> owner CHAR(16) NOT NULL,<br /> realm CHAR(64) NOT NULL,<br /> public ENUM(‘Y’,’N’) NOT NULL DEFAULT ‘Y’,<br /> status INT UNSIGNED NOT NULL DEFAULT ‘0’,<br /> title CHAR(255) NOT NULL,<br /> email CHAR(64),<br /> subtitle TEXT,<br /> info TEXT,<br /> theme CHAR(64),<br /> thanks_page CHAR(255),<br /> thank_head CHAR(255),<br /> thank_body TEXT,<br /> changed TIMESTAMP(14) DEFAULT ‘CURRENT_TIMESTAMP’,<br /> PRIMARY KEY (id),<br /> UNIQUE(name)<br /> );</p> <p>— # types of questions<br /> CREATE TABLE question_type (<br /> id INT UNSIGNED NOT NULL AUTO_INCREMENT,<br /> type CHAR(32) NOT NULL,<br /> has_choices ENUM(‘Y’,’N’) NOT NULL,<br /> response_table CHAR(32) NOT NULL,<br /> PRIMARY KEY (id)<br /> );</p> <p>— # table of the questions for all the surveys<br /> CREATE TABLE question (<br /> id INT UNSIGNED NOT NULL AUTO_INCREMENT,<br /> survey_id INT UNSIGNED NOT NULL,<br /> name CHAR(30) NOT NULL,<br /> type_id INT UNSIGNED NOT NULL,<br /> result_id INT UNSIGNED,<br /> length INT NOT NULL DEFAULT 0,<br /> precise INT NOT NULL DEFAULT 0,<br /> position INT UNSIGNED NOT NULL,<br /> content TEXT NOT NULL,<br /> required ENUM(‘Y’,’N’) NOT NULL DEFAULT ‘N’,<br /> deleted ENUM(‘Y’,’N’) NOT NULL DEFAULT ‘N’,<br /> public ENUM(‘Y’,’N’) NOT NULL DEFAULT ‘Y’,<br /> PRIMARY KEY (id),<br /> KEY `result_id` (`result_id`),<br /> KEY `survey_id` (`survey_id`)<br /> );</p> <p>— # table of the choices (possible answers) of each question<br /> CREATE TABLE question_choice (<br /> id INT UNSIGNED NOT NULL AUTO_INCREMENT,<br /> question_id INT UNSIGNED NOT NULL,<br /> content TEXT NOT NULL,<br /> value TEXT,<br /> PRIMARY KEY (id),<br /> KEY `question_id` (`question_id`)<br /> );</p> <p>— # access control to adding data to a form / survey<br /> CREATE TABLE access (<br /> id INT UNSIGNED NOT NULL AUTO_INCREMENT,<br /> survey_id INT UNSIGNED NOT NULL,<br /> realm CHAR(16),<br /> maxlogin INT UNSIGNED DEFAULT ‘0’,<br /> resume ENUM(‘Y’,’N’) NOT NULL DEFAULT ‘N’,<br /> navigate ENUM(‘Y’,’N’) NOT NULL DEFAULT ‘N’,<br /> PRIMARY KEY(id),<br /> KEY `survey_id` (`survey_id`)<br /> );</p> <p>— # this table holds info to distinguish one servey response from another<br /> — # (plus timestamp, and username if known)<br /> CREATE TABLE response (<br /> id INT UNSIGNED NOT NULL AUTO_INCREMENT,<br /> survey_id INT UNSIGNED NOT NULL,<br /> submitted TIMESTAMP(14) NOT NULL DEFAULT ”,<br /> complete ENUM(‘Y’,’N’) NOT NULL DEFAULT ‘N’,<br /> username CHAR(64),<br /> PRIMARY KEY (id),<br /> KEY `survey_id` (`survey_id`)<br /> );</p> <p>— # answers to boolean questions (yes/no)<br /> CREATE TABLE response_bool (<br /> response_id INT UNSIGNED NOT NULL,<br /> question_id INT UNSIGNED NOT NULL,<br /> choice_id ENUM(‘Y’,’N’) NOT NULL,<br /> PRIMARY KEY(response_id,question_id),<br /> KEY `response_id` (`response_id`),<br /> KEY `question_id` (`question_id`)<br /> );</p> <p>— # answers to single answer questions (radio, boolean, rate) (chose one of n)<br /> CREATE TABLE response_single (<br /> response_id INT UNSIGNED NOT NULL,<br /> question_id INT UNSIGNED NOT NULL,<br /> choice_id INT UNSIGNED NOT NULL,<br /> PRIMARY KEY(response_id,question_id),<br /> KEY `response_id` (`response_id`),<br /> KEY `question_id` (`question_id`)<br /> );</p> <p>— # answers to questions where multiple responses are allowed<br /> — # (checkbox, select multiple)<br /> CREATE TABLE response_multiple (<br /> id INT UNSIGNED NOT NULL AUTO_INCREMENT,<br /> response_id INT UNSIGNED NOT NULL,<br /> question_id INT UNSIGNED NOT NULL,<br /> choice_id INT UNSIGNED NOT NULL,<br /> PRIMARY KEY(id),<br /> KEY `response_id` (`response_id`),<br /> KEY `question_id` (`question_id`),<br /> KEY `choice_id` (`choice_id`)<br /> );</p> <p>— # answers to rank questions<br /> CREATE TABLE response_rank (<br /> response_id INT UNSIGNED NOT NULL,<br /> question_id INT UNSIGNED NOT NULL,<br /> choice_id INT UNSIGNED NOT NULL,<br /> rank INT NOT NULL,<br /> PRIMARY KEY(response_id,question_id,choice_id),<br /> KEY `response_id` (`response_id`),<br /> KEY `question_id` (`question_id`),<br /> KEY `choice_id` (`choice_id`)<br /> );</p> <p>— # answers to any fill in the blank or essay question<br /> CREATE TABLE response_text (<br /> response_id INT UNSIGNED NOT NULL,<br /> question_id INT UNSIGNED NOT NULL,<br /> response TEXT,<br /> PRIMARY KEY (response_id,question_id),<br /> KEY `response_id` (`response_id`),<br /> KEY `question_id` (`question_id`)<br /> );</p> <p>— # answers to any Other: ___ questions<br /> CREATE TABLE response_other (<br /> response_id INT UNSIGNED NOT NULL,<br /> question_id INT UNSIGNED NOT NULL,<br /> choice_id INT UNSIGNED NOT NULL,<br /> response TEXT,<br /> PRIMARY KEY (response_id, question_id, choice_id),<br /> KEY `response_id` (`response_id`),<br /> KEY `choice_id` (`choice_id`),<br /> KEY `question_id` (`question_id`)<br /> );</p> <p>— # answers to any date questions<br /> CREATE TABLE response_date (<br /> response_id INT UNSIGNED NOT NULL,<br /> question_id INT UNSIGNED NOT NULL,<br /> response DATE,<br /> PRIMARY KEY (response_id,question_id),<br /> KEY `response_id` (`response_id`),<br /> KEY `question_id` (`question_id`)<br /> );</p> <p>— # populate the types of questions<br /> INSERT INTO question_type VALUES (‘1′,’Yes/No’,’N’,’response_bool’);<br /> INSERT INTO question_type VALUES (‘2′,’Text Box’,’N’,’response_text’);<br /> INSERT INTO question_type VALUES (‘3′,’Essay Box’,’N’,’response_text’);<br /> INSERT INTO question_type VALUES (‘4′,’Radio Buttons’,’Y’,’response_single’);<br /> INSERT INTO question_type VALUES (‘5′,’Check Boxes’,’Y’,’response_multiple’);<br /> INSERT INTO question_type VALUES (‘6′,’Dropdown Box’,’Y’,’response_single’);<br /> — # INSERT INTO question_type VALUES (‘7′,’Rating’,’N’,’response_rank’);<br /> INSERT INTO question_type VALUES (‘8′,’Rate (scale 1..5)’,’Y’,’response_rank’);<br /> INSERT INTO question_type VALUES (‘9′,’Date’,’N’,’response_date’);<br /> INSERT INTO question_type VALUES (’10’,’Numeric’,’N’,’response_text’);<br /> INSERT INTO question_type VALUES (’99’,’Page Break’,’N’,”);<br /> INSERT INTO question_type VALUES (‘100′,’Section Text’,’N’,”);<br />