diff options
| author | Marvin Borner | 2020-10-02 21:12:23 +0200 |
|---|---|---|
| committer | Marvin Borner | 2020-10-02 21:12:23 +0200 |
| commit | e52bcb760f36b68495692ac5c5a5b68e8dafc33b (patch) | |
| tree | 9e386b6081a0516d2c475e187f0b744d65aae156 /tables.sql | |
| parent | 762794cecdb0b12b58db0de16d31c8d7c216171c (diff) | |
Added polls
Diffstat (limited to 'tables.sql')
| -rw-r--r-- | tables.sql | 31 |
1 files changed, 27 insertions, 4 deletions
@@ -9,10 +9,12 @@ CREATE TABLE IF NOT EXISTS theme( ) ENGINE=InnoDB DEFAULT CHARSET=utf8; -- TODO: Remove dropping --- DROP TABLE IF EXISTS quotes; --- DROP TABLE IF EXISTS users; --- DROP TABLE IF EXISTS types; --- DROP TABLE IF EXISTS class; +DROP TABLE IF EXISTS quotes; +DROP TABLE IF EXISTS ranking_questions; +DROP TABLE IF EXISTS ranking_answers; +DROP TABLE IF EXISTS users; +DROP TABLE IF EXISTS types; +DROP TABLE IF EXISTS class; CREATE TABLE IF NOT EXISTS types( id INTEGER PRIMARY KEY AUTO_INCREMENT, @@ -49,3 +51,24 @@ CREATE TABLE IF NOT EXISTS quotes( CONSTRAINT `fk_user_quote1` FOREIGN KEY (user_id) REFERENCES users (id), CONSTRAINT `fk_user_quote2` FOREIGN KEY (author_id) REFERENCES users (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE IF NOT EXISTS ranking_questions( + id INTEGER PRIMARY KEY AUTO_INCREMENT, + question VARCHAR(255) NOT NULL, + type_id INTEGER NOT NULL, + + UNIQUE KEY uk_question (question, type_id), + CONSTRAINT `fk_type_question` FOREIGN KEY (type_id) REFERENCES types (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; + +CREATE TABLE IF NOT EXISTS ranking_answers( + id INTEGER PRIMARY KEY AUTO_INCREMENT, + question_id INTEGER NOT NULL, + user_id INTEGER NOT NULL, -- Submitter + answer_id INTEGER NOT NULL, -- Selected pupil + + UNIQUE KEY uk_answer (question_id, user_id), + CONSTRAINT `fk_question_answer` FOREIGN KEY (question_id) REFERENCES users (id), + CONSTRAINT `fk_user_answer1` FOREIGN KEY (user_id) REFERENCES users (id), + CONSTRAINT `fk_user_answer2` FOREIGN KEY (answer_id) REFERENCES users (id) +) ENGINE=InnoDB DEFAULT CHARSET=utf8; |