Merge pull request #3 from marvinborner/motto

Motto

1 files changed, 28 insertions, 6 deletions

diff --git a/tables.sql b/tables.sql
index 6161b03..e630058 100644
--- a/tables.sql
+++ b/tables.sql
@@ -9,12 +9,14 @@ CREATE TABLE IF NOT EXISTS theme(
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 -- TODO: Remove dropping
-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;
+-- DROP TABLE IF EXISTS motto_votes;
+-- DROP TABLE IF EXISTS mottos;
+-- 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,
@@ -72,3 +74,23 @@ CREATE TABLE IF NOT EXISTS ranking_answers(
     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;
+
+
+CREATE TABLE IF NOT EXISTS mottos(
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    name VARCHAR(255) NOT NULL,
+    description VARCHAR(255) NOT NULL DEFAULT '',
+    
+    UNIQUE KEY main (name, description)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE IF NOT EXISTS motto_votes(
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    user_id INTEGER NOT NULL,
+    motto_id INTEGER NOT NULL,
+    votes SMALLINT UNSIGNED NOT NULL DEFAULT 0,
+
+    UNIQUE KEY uk_vote (user_id, motto_id),
+    CONSTRAINT `fk_voted_user` FOREIGN KEY (user_id) REFERENCES users (id),
+    CONSTRAINT `fk_voted_vote` FOREIGN KEY (motto_id) REFERENCES mottos (id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;