Merge pull request #4 from marvinborner/profile

Profile

1 files changed, 38 insertions, 0 deletions

diff --git a/tables.sql b/tables.sql
index 9f139f2..94594f7 100644
--- a/tables.sql
+++ b/tables.sql
@@ -4,6 +4,10 @@
 -- DROP TABLE IF EXISTS quotes;
 -- DROP TABLE IF EXISTS ranking_questions;
 -- DROP TABLE IF EXISTS ranking_answers;
+-- DROP TABLE IF EXISTS profile_comments;
+-- DROP TABLE IF EXISTS profile_answers;
+-- DROP TABLE IF EXISTS profile_questions;
+-- DROP TABLE IF EXISTS profile_input_types;
 -- DROP TABLE IF EXISTS users;
 -- DROP TABLE IF EXISTS types;
 -- DROP TABLE IF EXISTS class;
@@ -85,3 +89,37 @@ CREATE TABLE IF NOT EXISTS motto_votes(
     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;
+
+CREATE TABLE IF NOT EXISTS profile_input_types(
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    type VARCHAR(20) NOT NULL UNIQUE
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE IF NOT EXISTS profile_questions(
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    question VARCHAR(255) NOT NULL UNIQUE,
+    question_type INTEGER NOT NULL,
+
+    CONSTRAINT `fk_profile_question_type` FOREIGN KEY (question_type) REFERENCES profile_input_types (id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE IF NOT EXISTS profile_answers(
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    question_id INTEGER NOT NULL,
+    user_id INTEGER NOT NULL,
+    answer TEXT NULL, -- Consider VARCHAR
+
+    UNIQUE KEY uk_answer (question_id, user_id),
+    CONSTRAINT `fk_profile_user` FOREIGN KEY (user_id) REFERENCES users (id),
+    CONSTRAINT `fk_profile_question` FOREIGN KEY (question_id) REFERENCES profile_questions (id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
+
+CREATE TABLE IF NOT EXISTS profile_comments(
+    id INTEGER PRIMARY KEY AUTO_INCREMENT,
+    profile_id INTEGER NOT NULL, -- User's profile
+    user_id INTEGER NOT NULL, -- User who commented
+    comment TEXT NOT NULL,
+
+    CONSTRAINT `fk_user_profile` FOREIGN KEY (profile_id) REFERENCES users (id),
+    CONSTRAINT `fk_user_commenter` FOREIGN KEY (user_id) REFERENCES users (id)
+) ENGINE=InnoDB DEFAULT CHARSET=utf8;
\ No newline at end of file