\$\begingroup\$

I'm a big fan of David Allen's Getting Things Done, but the myriad software tools and sites I've tried haven't impressed me that much. That's why I've decided to write my own.

The concept

This app (I don't have a name for it yet.) pulls in your twitter stream, Facebook news feeds, email, RSS feeds, and more and treats them like items in your GTD inbox, sorted by importance, dependent on who sent you the message. Once everything has been gathered, it's your job to look at each item and decide what to do with it.

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0; SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL'; CREATE SCHEMA IF NOT EXISTS `DoneBox` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ; USE `DoneBox` ; -- ----------------------------------------------------- -- Table `DoneBox`.`users` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `DoneBox`.`users` ( `id` CHAR(16) BINARY NOT NULL , `username` VARCHAR(50) NULL , `password` VARCHAR(150) NULL , `email` VARCHAR(100) NULL , `recovery_answer_1` TEXT NULL , `recovery_answer_2` TEXT NULL , `phone` VARCHAR(45) NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; CREATE UNIQUE INDEX `username_UNIQUE` ON `DoneBox`.`users` (`username` ASC) ; -- ----------------------------------------------------- -- Table `DoneBox`.`Imap` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `DoneBox`.`Imap` ( `id` CHAR(16) BINARY NOT NULL , `host` VARCHAR(100) NULL , `port` INT NULL , `ssl` TINYINT(1) NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `DoneBox`.`smtp` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `DoneBox`.`smtp` ( `id` CHAR(16) BINARY NOT NULL , `host` VARCHAR(100) NULL , `port` INT NULL , `ssl` TINYINT(1) NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `DoneBox`.`pop3` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `DoneBox`.`pop3` ( `id` CHAR(16) BINARY NOT NULL , `host` VARCHAR(100) NULL , `port` INT NULL , `ssl` TINYINT(1) NULL , PRIMARY KEY (`id`) ) ENGINE = InnoDB; -- ----------------------------------------------------- -- Table `DoneBox`.`email_provider` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `DoneBox`.`email_provider` ( `id` CHAR(16) NOT NULL , `domain` VARCHAR(100) NULL , `long_domain` VARCHAR(100) NULL , `Imap_id` CHAR(16) BINARY NOT NULL , `pop3_id` CHAR(16) BINARY NOT NULL , `smtp_id` CHAR(16) BINARY NOT NULL , PRIMARY KEY (`id`, `Imap_id`, `pop3_id`, `smtp_id`) , CONSTRAINT `fk_email_provider_Imap1` FOREIGN KEY (`Imap_id` ) REFERENCES `DoneBox`.`Imap` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_email_provider_pop31` FOREIGN KEY (`pop3_id` ) REFERENCES `DoneBox`.`pop3` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_email_provider_smtp1` FOREIGN KEY (`smtp_id` ) REFERENCES `DoneBox`.`smtp` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE INDEX `fk_email_provider_Imap1` ON `DoneBox`.`email_provider` (`Imap_id` ASC) ; CREATE INDEX `fk_email_provider_pop31` ON `DoneBox`.`email_provider` (`pop3_id` ASC) ; CREATE INDEX `fk_email_provider_smtp1` ON `DoneBox`.`email_provider` (`smtp_id` ASC) ; -- ----------------------------------------------------- -- Table `DoneBox`.`email_account` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `DoneBox`.`email_account` ( `id` CHAR(16) BINARY NOT NULL , `email` VARCHAR(100) NULL , `password` VARCHAR(150) NULL , `signature` VARCHAR(45) NULL , `email_provider_id` CHAR(16) NOT NULL , `users_id` CHAR(16) BINARY NOT NULL , PRIMARY KEY (`id`, `email_provider_id`, `users_id`) , CONSTRAINT `fk_email_account_email_provider1` FOREIGN KEY (`email_provider_id` ) REFERENCES `DoneBox`.`email_provider` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_email_account_users1` FOREIGN KEY (`users_id` ) REFERENCES `DoneBox`.`users` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE INDEX `fk_email_account_email_provider1` ON `DoneBox`.`email_account` (`email_provider_id` ASC) ; CREATE INDEX `fk_email_account_users1` ON `DoneBox`.`email_account` (`users_id` ASC) ; -- ----------------------------------------------------- -- Table `DoneBox`.`contexts` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `DoneBox`.`contexts` ( `id` CHAR(16) BINARY NOT NULL , `name` VARCHAR(100) NULL , `users_id` CHAR(16) BINARY NOT NULL , PRIMARY KEY (`id`, `users_id`) , CONSTRAINT `fk_contexts_users1` FOREIGN KEY (`users_id` ) REFERENCES `DoneBox`.`users` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE INDEX `fk_contexts_users1` ON `DoneBox`.`contexts` (`users_id` ASC) ; -- ----------------------------------------------------- -- Table `DoneBox`.`projects` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `DoneBox`.`projects` ( `id` CHAR(16) BINARY NOT NULL , `name` VARCHAR(100) NULL , `description` LONGTEXT NULL , `contexts_id` CHAR(16) BINARY NOT NULL , `users_id` CHAR(16) BINARY NOT NULL , PRIMARY KEY (`id`, `contexts_id`, `users_id`) , CONSTRAINT `fk_projects_contexts1` FOREIGN KEY (`contexts_id` ) REFERENCES `DoneBox`.`contexts` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_projects_users1` FOREIGN KEY (`users_id` ) REFERENCES `DoneBox`.`users` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE INDEX `fk_projects_contexts1` ON `DoneBox`.`projects` (`contexts_id` ASC) ; CREATE INDEX `fk_projects_users1` ON `DoneBox`.`projects` (`users_id` ASC) ; -- ----------------------------------------------------- -- Table `DoneBox`.`email_messages` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `DoneBox`.`email_messages` ( `id` CHAR(16) NOT NULL , `sender` VARCHAR(100) NULL , `sent_on` DATETIME NULL , `subject` VARCHAR(45) NULL , `body` LONGTEXT NULL , `is_unread` TINYINT(1) NULL , `email_account_id` CHAR(16) BINARY NOT NULL , `users_id` CHAR(16) BINARY NOT NULL , `projects_id` CHAR(16) BINARY NOT NULL , `contexts_id` CHAR(16) BINARY NOT NULL , PRIMARY KEY (`id`, `email_account_id`, `users_id`, `projects_id`, `contexts_id`) , CONSTRAINT `fk_email_messages_email_account1` FOREIGN KEY (`email_account_id` ) REFERENCES `DoneBox`.`email_account` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_email_messages_users1` FOREIGN KEY (`users_id` ) REFERENCES `DoneBox`.`users` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_email_messages_projects1` FOREIGN KEY (`projects_id` ) REFERENCES `DoneBox`.`projects` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_email_messages_contexts1` FOREIGN KEY (`contexts_id` ) REFERENCES `DoneBox`.`contexts` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE INDEX `fk_email_messages_email_account1` ON `DoneBox`.`email_messages` (`email_account_id` ASC) ; CREATE INDEX `fk_email_messages_users1` ON `DoneBox`.`email_messages` (`users_id` ASC) ; CREATE INDEX `fk_email_messages_projects1` ON `DoneBox`.`email_messages` (`projects_id` ASC) ; CREATE INDEX `fk_email_messages_contexts1` ON `DoneBox`.`email_messages` (`contexts_id` ASC) ; -- ----------------------------------------------------- -- Table `DoneBox`.`social_account` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `DoneBox`.`social_account` ( `id` CHAR(16) BINARY NOT NULL , `service` VARCHAR(100) NULL , `username` VARCHAR(100) NULL , `send` INT(11) NULL , `receive` INT(11) NULL , `key` VARCHAR(200) NULL , `json` LONGTEXT NULL , `users_id` CHAR(16) BINARY NOT NULL , PRIMARY KEY (`id`, `users_id`) , CONSTRAINT `fk_social_account_users1` FOREIGN KEY (`users_id` ) REFERENCES `DoneBox`.`users` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE INDEX `fk_social_account_users1` ON `DoneBox`.`social_account` (`users_id` ASC) ; -- ----------------------------------------------------- -- Table `DoneBox`.`social_messages` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `DoneBox`.`social_messages` ( `id` CHAR(16) BINARY NOT NULL , `sender` VARCHAR(200) NULL , `messaage_id` VARCHAR(200) NULL , `operation` TEXT NULL , `transient` TEXT NULL , `stream` VARCHAR(45) NULL , `time` TIME NULL , `from_me` INT(11) NULL , `to_me` VARCHAR(45) NULL , `json` VARCHAR(45) NULL , `social_account_id` CHAR(16) BINARY NOT NULL , `users_id` CHAR(16) BINARY NOT NULL , `projects_id` CHAR(16) BINARY NOT NULL , `contexts_id` CHAR(16) BINARY NOT NULL , PRIMARY KEY (`id`, `social_account_id`, `users_id`, `projects_id`, `contexts_id`) , CONSTRAINT `fk_social_messages_social_account1` FOREIGN KEY (`social_account_id` ) REFERENCES `DoneBox`.`social_account` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_social_messages_users1` FOREIGN KEY (`users_id` ) REFERENCES `DoneBox`.`users` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_social_messages_contexts1` FOREIGN KEY (`contexts_id` ) REFERENCES `DoneBox`.`contexts` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_social_messages_projects1` FOREIGN KEY (`projects_id` ) REFERENCES `DoneBox`.`projects` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE INDEX `fk_social_messages_social_account1` ON `DoneBox`.`social_messages` (`social_account_id` ASC) ; CREATE INDEX `fk_social_messages_users1` ON `DoneBox`.`social_messages` (`users_id` ASC) ; CREATE INDEX `fk_social_messages_contexts1` ON `DoneBox`.`social_messages` (`contexts_id` ASC) ; CREATE INDEX `fk_social_messages_projects1` ON `DoneBox`.`social_messages` (`projects_id` ASC) ; -- ----------------------------------------------------- -- Table `DoneBox`.`syndicate_feeds` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `DoneBox`.`syndicate_feeds` ( `id` CHAR(16) BINARY NOT NULL , `name` VARCHAR(100) NULL , `url` VARCHAR(200) NULL , `users_id` CHAR(16) BINARY NOT NULL , PRIMARY KEY (`id`, `users_id`) , CONSTRAINT `fk_syndicate_feeds_users1` FOREIGN KEY (`users_id` ) REFERENCES `DoneBox`.`users` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE INDEX `fk_syndicate_feeds_users1` ON `DoneBox`.`syndicate_feeds` (`users_id` ASC) ; -- ----------------------------------------------------- -- Table `DoneBox`.`syndicate_items` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `DoneBox`.`syndicate_items` ( `id` CHAR(16) BINARY NOT NULL , `title` VARCHAR(100) NULL , `date` DATE NULL , `url` VARCHAR(200) NULL , `body` LONGTEXT NULL , `syndicate_feeds_id` CHAR(16) BINARY NOT NULL , `users_id` CHAR(16) BINARY NOT NULL , `projects_id` CHAR(16) BINARY NOT NULL , `contexts_id` CHAR(16) BINARY NOT NULL , PRIMARY KEY (`id`, `syndicate_feeds_id`, `users_id`, `projects_id`, `contexts_id`) , CONSTRAINT `fk_syndicate_items_syndicate_feeds1` FOREIGN KEY (`syndicate_feeds_id` ) REFERENCES `DoneBox`.`syndicate_feeds` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_syndicate_items_users1` FOREIGN KEY (`users_id` ) REFERENCES `DoneBox`.`users` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_syndicate_items_projects1` FOREIGN KEY (`projects_id` ) REFERENCES `DoneBox`.`projects` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `fk_syndicate_items_contexts1` FOREIGN KEY (`contexts_id` ) REFERENCES `DoneBox`.`contexts` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE INDEX `fk_syndicate_items_syndicate_feeds1` ON `DoneBox`.`syndicate_items` (`syndicate_feeds_id` ASC) ; CREATE INDEX `fk_syndicate_items_users1` ON `DoneBox`.`syndicate_items` (`users_id` ASC) ; CREATE INDEX `fk_syndicate_items_projects1` ON `DoneBox`.`syndicate_items` (`projects_id` ASC) ; CREATE INDEX `fk_syndicate_items_contexts1` ON `DoneBox`.`syndicate_items` (`contexts_id` ASC) ; -- ----------------------------------------------------- -- Table `DoneBox`.`activation_profile` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `DoneBox`.`activation_profile` ( `id` CHAR(16) BINARY NOT NULL , `activation_code` VARCHAR(40) NULL , `date` DATE NULL , `users_id` CHAR(16) BINARY NOT NULL , PRIMARY KEY (`id`, `users_id`) , CONSTRAINT `fk_activation_profile_users1` FOREIGN KEY (`users_id` ) REFERENCES `DoneBox`.`users` (`id` ) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE = InnoDB; CREATE INDEX `fk_activation_profile_users1` ON `DoneBox`.`activation_profile` (`users_id` ASC) ; CREATE USER `levi` IDENTIFIED BY '<snip>'; grant DELETE on TABLE `DoneBox`.`Imap` to levi; grant INSERT on TABLE `DoneBox`.`Imap` to levi; grant SELECT on TABLE `DoneBox`.`Imap` to levi; grant UPDATE on TABLE `DoneBox`.`Imap` to levi; grant DELETE on TABLE `DoneBox`.`social_messages` to levi; grant INSERT on TABLE `DoneBox`.`social_messages` to levi; grant SELECT on TABLE `DoneBox`.`social_messages` to levi; grant UPDATE on TABLE `DoneBox`.`social_messages` to levi; grant DELETE on TABLE `DoneBox`.`contexts` to levi; grant SELECT on TABLE `DoneBox`.`contexts` to levi; grant UPDATE on TABLE `DoneBox`.`contexts` to levi; grant INSERT on TABLE `DoneBox`.`contexts` to levi; grant DELETE on TABLE `DoneBox`.`pop3` to levi; grant INSERT on TABLE `DoneBox`.`pop3` to levi; grant SELECT on TABLE `DoneBox`.`pop3` to levi; grant UPDATE on TABLE `DoneBox`.`pop3` to levi; grant DELETE on TABLE `DoneBox`.`syndicate_feeds` to levi; grant SELECT on TABLE `DoneBox`.`syndicate_feeds` to levi; grant UPDATE on TABLE `DoneBox`.`syndicate_feeds` to levi; grant INSERT on TABLE `DoneBox`.`syndicate_feeds` to levi; grant DELETE on TABLE `DoneBox`.`email_account` to levi; grant SELECT on TABLE `DoneBox`.`email_account` to levi; grant UPDATE on TABLE `DoneBox`.`email_account` to levi; grant INSERT on TABLE `DoneBox`.`email_account` to levi; grant DELETE on TABLE `DoneBox`.`projects` to levi; grant INSERT on TABLE `DoneBox`.`projects` to levi; grant SELECT on TABLE `DoneBox`.`projects` to levi; grant UPDATE on TABLE `DoneBox`.`projects` to levi; grant DELETE on TABLE `DoneBox`.`syndicate_items` to levi; grant INSERT on TABLE `DoneBox`.`syndicate_items` to levi; grant SELECT on TABLE `DoneBox`.`syndicate_items` to levi; grant UPDATE on TABLE `DoneBox`.`syndicate_items` to levi; grant DELETE on TABLE `DoneBox`.`email_messages` to levi; grant INSERT on TABLE `DoneBox`.`email_messages` to levi; grant SELECT on TABLE `DoneBox`.`email_messages` to levi; grant UPDATE on TABLE `DoneBox`.`email_messages` to levi; grant DELETE on TABLE `DoneBox`.`smtp` to levi; grant INSERT on TABLE `DoneBox`.`smtp` to levi; grant SELECT on TABLE `DoneBox`.`smtp` to levi; grant UPDATE on TABLE `DoneBox`.`smtp` to levi; grant DELETE on TABLE `DoneBox`.`users` to levi; grant INSERT on TABLE `DoneBox`.`users` to levi; grant SELECT on TABLE `DoneBox`.`users` to levi; grant UPDATE on TABLE `DoneBox`.`users` to levi; grant DELETE on TABLE `DoneBox`.`email_provider` to levi; grant INSERT on TABLE `DoneBox`.`email_provider` to levi; grant SELECT on TABLE `DoneBox`.`email_provider` to levi; grant UPDATE on TABLE `DoneBox`.`email_provider` to levi; grant DELETE on TABLE `DoneBox`.`social_account` to levi; grant INSERT on TABLE `DoneBox`.`social_account` to levi; grant SELECT on TABLE `DoneBox`.`social_account` to levi; grant UPDATE on TABLE `DoneBox`.`social_account` to levi; SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

MySQL workbench file I used to generate this code.

This database will be deployed to Amazon RDS.

I'm using a clojure library called korma to run queries.

The Imap, smtp, pop3, and email_provider tables are used to keep a local email configuration cache, these could probably be merged.

I'm using a GUID as the primary key for all my tables.

Does this design make sense? Are there any places I can make improvements? Are the permissions I have set enough? This is my first attempt at trying to design a database for a web app, so any suggestions and advice are greatly appreciated. Thank you for your time and consideration.