{"id":64,"date":"2006-01-10T22:41:00","date_gmt":"2006-01-10T22:41:00","guid":{"rendered":"http:\/\/blog.trungson.com\/?p=64"},"modified":"2006-01-10T22:41:00","modified_gmt":"2006-01-10T22:41:00","slug":"phpesp-1-8-pollsurvey-script-db-design","status":"publish","type":"post","link":"http:\/\/blog.trungson.com\/?p=64","title":{"rendered":"phpESP 1.8 &#8211; Poll\/Survey Script &#8211; DB Design"},"content":{"rendered":"<p><textarea name=\"code\" class=\"sql\"><br \/>\n&#8212; # table of different surveys available<br \/>\nCREATE TABLE survey (<br \/>\n id   INT UNSIGNED NOT NULL AUTO_INCREMENT,<br \/>\n name  CHAR(64) NOT NULL,<br \/>\n owner  CHAR(16) NOT NULL,<br \/>\n realm  CHAR(64) NOT NULL,<br \/>\n public  ENUM(&#8216;Y&#8217;,&#8217;N&#8217;) NOT NULL DEFAULT &#8216;Y&#8217;,<br \/>\n status  INT UNSIGNED NOT NULL DEFAULT &#8216;0&#8217;,<br \/>\n title  CHAR(255) NOT NULL,<br \/>\n email  CHAR(64),<br \/>\n subtitle TEXT,<br \/>\n info  TEXT,<br \/>\n theme  CHAR(64),<br \/>\n thanks_page CHAR(255),<br \/>\n thank_head CHAR(255),<br \/>\n thank_body TEXT,<br \/>\n changed         TIMESTAMP(14) DEFAULT &#8216;CURRENT_TIMESTAMP&#8217;,<br \/>\n PRIMARY KEY (id),<br \/>\n UNIQUE(name)<br \/>\n);<\/p>\n<p>&#8212; # types of questions<br \/>\nCREATE TABLE question_type (<br \/>\n id  INT UNSIGNED NOT NULL AUTO_INCREMENT,<br \/>\n type  CHAR(32) NOT NULL,<br \/>\n has_choices ENUM(&#8216;Y&#8217;,&#8217;N&#8217;) NOT NULL,<br \/>\n response_table CHAR(32) NOT NULL,<br \/>\n PRIMARY KEY (id)<br \/>\n);<\/p>\n<p>&#8212; # table of the questions for all the surveys<br \/>\nCREATE TABLE question (<br \/>\n id  INT UNSIGNED NOT NULL AUTO_INCREMENT,<br \/>\n survey_id INT UNSIGNED NOT NULL,<br \/>\n name  CHAR(30) NOT NULL,<br \/>\n type_id  INT UNSIGNED NOT NULL,<br \/>\n result_id INT UNSIGNED,<br \/>\n length  INT NOT NULL DEFAULT 0,<br \/>\n precise  INT NOT NULL DEFAULT 0,<br \/>\n position INT UNSIGNED NOT NULL,<br \/>\n content  TEXT NOT NULL,<br \/>\n required ENUM(&#8216;Y&#8217;,&#8217;N&#8217;) NOT NULL DEFAULT &#8216;N&#8217;,<br \/>\n deleted  ENUM(&#8216;Y&#8217;,&#8217;N&#8217;) NOT NULL DEFAULT &#8216;N&#8217;,<br \/>\n public  ENUM(&#8216;Y&#8217;,&#8217;N&#8217;) NOT NULL DEFAULT &#8216;Y&#8217;,<br \/>\n PRIMARY KEY (id),<br \/>\n KEY `result_id` (`result_id`),<br \/>\n KEY `survey_id` (`survey_id`)<br \/>\n);<\/p>\n<p>&#8212; # table of the choices (possible answers) of each question<br \/>\nCREATE TABLE question_choice (<br \/>\n id  INT UNSIGNED NOT NULL AUTO_INCREMENT,<br \/>\n question_id INT UNSIGNED NOT NULL,<br \/>\n content  TEXT NOT NULL,<br \/>\n value  TEXT,<br \/>\n PRIMARY KEY (id),<br \/>\n KEY `question_id` (`question_id`)<br \/>\n);<\/p>\n<p>&#8212; # access control to adding data to a form \/ survey<br \/>\nCREATE TABLE access (<br \/>\n id  INT UNSIGNED NOT NULL AUTO_INCREMENT,<br \/>\n survey_id INT UNSIGNED NOT NULL,<br \/>\n realm  CHAR(16),<br \/>\n maxlogin INT UNSIGNED DEFAULT &#8216;0&#8217;,<br \/>\n        resume  ENUM(&#8216;Y&#8217;,&#8217;N&#8217;) NOT NULL DEFAULT &#8216;N&#8217;,<br \/>\n        navigate ENUM(&#8216;Y&#8217;,&#8217;N&#8217;) NOT NULL DEFAULT &#8216;N&#8217;,<br \/>\n PRIMARY KEY(id),<br \/>\n KEY `survey_id` (`survey_id`)<br \/>\n);<\/p>\n<p>&#8212; # this table holds info to distinguish one servey response from another<br \/>\n&#8212; # (plus timestamp, and username if known)<br \/>\nCREATE TABLE response (<br \/>\n id   INT UNSIGNED NOT NULL AUTO_INCREMENT,<br \/>\n survey_id INT UNSIGNED NOT NULL,<br \/>\n submitted TIMESTAMP(14) NOT NULL DEFAULT &#8221;,<br \/>\n complete ENUM(&#8216;Y&#8217;,&#8217;N&#8217;) NOT NULL DEFAULT &#8216;N&#8217;,<br \/>\n username CHAR(64),<br \/>\n PRIMARY KEY (id),<br \/>\n KEY `survey_id` (`survey_id`)<br \/>\n);<\/p>\n<p>&#8212; # answers to boolean questions (yes\/no)<br \/>\nCREATE TABLE response_bool (<br \/>\n response_id INT UNSIGNED NOT NULL,<br \/>\n question_id INT UNSIGNED NOT NULL,<br \/>\n choice_id ENUM(&#8216;Y&#8217;,&#8217;N&#8217;) NOT NULL,<br \/>\n PRIMARY KEY(response_id,question_id),<br \/>\n KEY `response_id` (`response_id`),<br \/>\n KEY `question_id` (`question_id`)<br \/>\n);<\/p>\n<p>&#8212; # answers to single answer questions (radio, boolean, rate) (chose one of n)<br \/>\nCREATE TABLE response_single (<br \/>\n response_id INT UNSIGNED NOT NULL,<br \/>\n question_id INT UNSIGNED NOT NULL,<br \/>\n choice_id INT UNSIGNED NOT NULL,<br \/>\n PRIMARY KEY(response_id,question_id),<br \/>\n KEY `response_id` (`response_id`),<br \/>\n KEY `question_id` (`question_id`)<br \/>\n);<\/p>\n<p>&#8212; # answers to questions where multiple responses are allowed<br \/>\n&#8212; # (checkbox, select multiple)<br \/>\nCREATE TABLE response_multiple (<br \/>\n id   INT UNSIGNED NOT NULL AUTO_INCREMENT,<br \/>\n response_id INT UNSIGNED NOT NULL,<br \/>\n question_id INT UNSIGNED NOT NULL,<br \/>\n choice_id INT UNSIGNED NOT NULL,<br \/>\n PRIMARY KEY(id),<br \/>\n KEY `response_id` (`response_id`),<br \/>\n KEY `question_id` (`question_id`),<br \/>\n KEY `choice_id` (`choice_id`)<br \/>\n);<\/p>\n<p>&#8212; # answers to rank questions<br \/>\nCREATE TABLE response_rank (<br \/>\n response_id INT UNSIGNED NOT NULL,<br \/>\n question_id INT UNSIGNED NOT NULL,<br \/>\n choice_id INT UNSIGNED NOT NULL,<br \/>\n rank  INT NOT NULL,<br \/>\n PRIMARY KEY(response_id,question_id,choice_id),<br \/>\n KEY `response_id` (`response_id`),<br \/>\n KEY `question_id` (`question_id`),<br \/>\n KEY `choice_id` (`choice_id`)<br \/>\n);<\/p>\n<p>&#8212; # answers to any fill in the blank or essay question<br \/>\nCREATE TABLE response_text (<br \/>\n response_id INT UNSIGNED NOT NULL,<br \/>\n question_id INT UNSIGNED NOT NULL,<br \/>\n response TEXT,<br \/>\n PRIMARY KEY (response_id,question_id),<br \/>\n KEY `response_id` (`response_id`),<br \/>\n KEY `question_id` (`question_id`)<br \/>\n);<\/p>\n<p>&#8212; # answers to any Other: ___ questions<br \/>\nCREATE TABLE response_other (<br \/>\n response_id INT UNSIGNED NOT NULL,<br \/>\n question_id INT UNSIGNED NOT NULL,<br \/>\n choice_id INT UNSIGNED NOT NULL,<br \/>\n response TEXT,<br \/>\n PRIMARY KEY (response_id, question_id, choice_id),<br \/>\n KEY `response_id` (`response_id`),<br \/>\n KEY `choice_id` (`choice_id`),<br \/>\n KEY `question_id` (`question_id`)<br \/>\n);<\/p>\n<p>&#8212; # answers to any date questions<br \/>\nCREATE TABLE response_date (<br \/>\n response_id INT UNSIGNED NOT NULL,<br \/>\n question_id INT UNSIGNED NOT NULL,<br \/>\n response DATE,<br \/>\n PRIMARY KEY (response_id,question_id),<br \/>\n KEY `response_id` (`response_id`),<br \/>\n KEY `question_id` (`question_id`)<br \/>\n);<\/p>\n<p>&#8212; # populate the types of questions<br \/>\nINSERT INTO question_type VALUES (&#8216;1&#8242;,&#8217;Yes\/No&#8217;,&#8217;N&#8217;,&#8217;response_bool&#8217;);<br \/>\nINSERT INTO question_type VALUES (&#8216;2&#8242;,&#8217;Text Box&#8217;,&#8217;N&#8217;,&#8217;response_text&#8217;);<br \/>\nINSERT INTO question_type VALUES (&#8216;3&#8242;,&#8217;Essay Box&#8217;,&#8217;N&#8217;,&#8217;response_text&#8217;);<br \/>\nINSERT INTO question_type VALUES (&#8216;4&#8242;,&#8217;Radio Buttons&#8217;,&#8217;Y&#8217;,&#8217;response_single&#8217;);<br \/>\nINSERT INTO question_type VALUES (&#8216;5&#8242;,&#8217;Check Boxes&#8217;,&#8217;Y&#8217;,&#8217;response_multiple&#8217;);<br \/>\nINSERT INTO question_type VALUES (&#8216;6&#8242;,&#8217;Dropdown Box&#8217;,&#8217;Y&#8217;,&#8217;response_single&#8217;);<br \/>\n&#8212; # INSERT INTO question_type VALUES (&#8216;7&#8242;,&#8217;Rating&#8217;,&#8217;N&#8217;,&#8217;response_rank&#8217;);<br \/>\nINSERT INTO question_type VALUES (&#8216;8&#8242;,&#8217;Rate (scale 1..5)&#8217;,&#8217;Y&#8217;,&#8217;response_rank&#8217;);<br \/>\nINSERT INTO question_type VALUES (&#8216;9&#8242;,&#8217;Date&#8217;,&#8217;N&#8217;,&#8217;response_date&#8217;);<br \/>\nINSERT INTO question_type VALUES (&#8217;10&#8217;,&#8217;Numeric&#8217;,&#8217;N&#8217;,&#8217;response_text&#8217;);<br \/>\nINSERT INTO question_type VALUES (&#8217;99&#8217;,&#8217;Page Break&#8217;,&#8217;N&#8217;,&#8221;);<br \/>\nINSERT INTO question_type VALUES (&#8216;100&#8242;,&#8217;Section Text&#8217;,&#8217;N&#8217;,&#8221;);<br \/>\n<\/textarea><\/p>\n","protected":false},"excerpt":{"rendered":"<p>&#8212; # table of different surveys available CREATE TABLE survey ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(64) NOT NULL, owner CHAR(16) NOT NULL, realm CHAR(64) NOT NULL, public ENUM(&#8216;Y&#8217;,&#8217;N&#8217;) NOT NULL DEFAULT &#8216;Y&#8217;, status INT UNSIGNED NOT NULL DEFAULT &#8216;0&#8217;, title CHAR(255) NOT NULL, email CHAR(64), subtitle TEXT, info TEXT, theme CHAR(64), thanks_page [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"_links":{"self":[{"href":"http:\/\/blog.trungson.com\/index.php?rest_route=\/wp\/v2\/posts\/64"}],"collection":[{"href":"http:\/\/blog.trungson.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/blog.trungson.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/blog.trungson.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/blog.trungson.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=64"}],"version-history":[{"count":0,"href":"http:\/\/blog.trungson.com\/index.php?rest_route=\/wp\/v2\/posts\/64\/revisions"}],"wp:attachment":[{"href":"http:\/\/blog.trungson.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=64"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/blog.trungson.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=64"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/blog.trungson.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=64"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}