1 #
RENAME TABLE surveys
TO old_surveys
,
2 # survey_answers
TO old_survey_answers
,
3 # survey_votes
TO old_survey_votes
;
5 DROP TABLE IF EXISTS survey_vote_answers
;
6 DROP TABLE IF EXISTS survey_voters
;
7 DROP TABLE IF EXISTS survey_votes
;
8 DROP TABLE IF EXISTS survey_questions
;
9 DROP TABLE IF EXISTS surveys
;
11 CREATE TABLE surveys (
12 id INT(11) UNSIGNED
NOT NULL auto_increment
,
13 uid
INT(11) UNSIGNED
NOT NULL,
14 shortname
VARCHAR(32) NOT NULL,
15 title
VARCHAR(255) NOT NULL,
16 description
TEXT NOT NULL,
20 voters
TEXT DEFAULT NULL COMMENT "Filter users who can vote",
21 viewers
TEXT DEFAULT NULL COMMENT "Filter users who can see the results",
23 flags
SET('validated', 'anonymous'),
26 UNIQUE KEY shortname (shortname
),
27 FOREIGN KEY (uid
) REFERENCES accounts (uid
)
30 ) ENGINE
=InnoDB
, CHARSET
=utf8
, COMMENT="Describe a survey";
32 CREATE TABLE survey_questions (
33 sid
INT(11) UNSIGNED
NOT NULL,
34 qid
INT(11) UNSIGNED
NOT NULL,
35 parent
INT(11) UNSIGNED
DEFAULT NULL COMMENT "Id of the parent question",
37 type VARCHAR(32) NOT NULL, -- XXX: Use an enum of possible types?
38 label
TEXT DEFAULT NULL,
39 parameters TEXT DEFAULT NULL COMMENT "Parameters of the question",
40 flags
SET('multiple', 'mandatory', 'noanswer') NOT NULL DEFAULT '',
42 PRIMARY KEY id (sid
, qid
),
43 FOREIGN KEY (sid
) REFERENCES surveys (id)
46 ) ENGINE
=InnoDB
, CHARSET
=utf8
, COMMENT="Describe the questions of the surveys";
48 CREATE TABLE survey_votes (
49 vid
INT(11) UNSIGNED
NOT NULL auto_increment
,
50 sid
INT(11) UNSIGNED
NOT NULL,
52 PRIMARY KEY vid (vid
),
53 FOREIGN KEY (sid
) REFERENCES surveys (id)
56 ) ENGINE
=InnoDB
, CHARSET
=utf8
, COMMENT="Identify unique votes";
58 CREATE TABLE survey_voters (
59 sid
INT(11) UNSIGNED
NOT NULL,
60 uid
INT(11) UNSIGNED
NOT NULL,
61 vid
INT(11) UNSIGNED
DEFAULT NULL, -- NULL for anonymous votes
63 PRIMARY KEY id (sid
, uid
),
64 FOREIGN KEY (uid
) REFERENCES accounts (uid
)
67 FOREIGN KEY (sid
) REFERENCES surveys (id)
70 ) ENGINE
=InnoDB
, CHARSET
=utf8
, COMMENT="List voters";
72 CREATE TABLE survey_vote_answers (
73 sid
INT(11) UNSIGNED
NOT NULL,
74 qid
INT(11) UNSIGNED
NOT NULL,
75 vid
INT(11) UNSIGNED
NOT NULL,
77 answer
TEXT DEFAULT NULL,
79 PRIMARY KEY id (sid
, vid
, qid
),
80 FOREIGN KEY (vid
) REFERENCES survey_votes (vid
)
83 FOREIGN KEY (sid
, qid
) REFERENCES survey_questions (sid
, qid
)
86 ) ENGINE
=InnoDB
, CHARSET
=utf8
, COMMENT="Answers to the surveys";
88 -- vim:set syntax=mysql: