1 files changed, 25 insertions, 8 deletions
diff --git a/db/0-create.psql b/db/0-create.psql
index 00ea012..28d9b45 100755..100644
--- a/db/0-create.psql
+++ b/db/0-create.psql
@@ -29,6 +29,10 @@ CREATE TABLE messages (
     created_on timestamp NOT NULL DEFAULT now(),
     is_image bool NOT NULL
 );
+CREATE INDEX messages_user_id_idx ON messages (user_id);
+CREATE INDEX messages_room_id_idx ON messages (room_id);
+CREATE INDEX messages_created_on_idx ON messages (created_on);
+CREATE INDEX messages_is_image_idx ON messages (is_image);
 
 -- Queries to support:
 -- 1) What are my favorite images? (By room, time, or author)
@@ -40,16 +44,29 @@ CREATE TABLE favorites (
     message_id integer NOT NULL REFERENCES messages,
     created_on timestamp NOT NULL DEFAULT now()
 );
-
-CREATE INDEX user_id_idx ON messages (user_id);
-CREATE INDEX room_id_idx ON messages (room_id);
-CREATE INDEX created_on_idx ON messages (created_on);
-CREATE INDEX is_image_idx ON messages (is_image);
-
 CREATE INDEX src_user_id_idx ON favorites (src_user_id);
 CREATE INDEX favorites_created_on_idx on favorites (created_on);
 
+-- Queries to support:
+-- 1) What messages are tagged x? (ordered by time, popularity)
+-- 2) What did I tag x?
+-- 3) Which of my things are tagged x?
+-- 4) Which of user u's messages are tagged x?
+-- 5) What are the popular tags lately?
+
+CREATE TABLE tags (
+  user_id integer NOT NULL REFERENCES users,
+  message_id integer NOT NULL REFERENCES messages,
+  created_on timestamp NOT NULL DEFAULT now(),
+  tag text NOT NULL,
+  PRIMARY KEY (user_id, message_id, tag)
+);
+CREATE INDEX tags_user_id_idx ON tags (user_id);
+CREATE INDEX tags_message_id_idx ON tags (message_id);
+CREATE INDEX tags_tag_idx ON tags (lower(tag));
+
 INSERT INTO rooms (key, name, description, admin_only) 
-       VALUES ('RoomA', 'Room A', 'Hangout', false);
+       VALUES ('dumpfm', 'Room A', 'Hangout', false);
 INSERT INTO rooms (key, name, description, admin_only)
-       VALUES ('VIP', 'The VIP Room', 'Command Post', true);
-\ No newline at end of file
+       VALUES ('VIP', 'The VIP Room', 'Command Post', true);
+