Please review my database design for an online organization tool

Question

The problem I'm trying to solve.

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: http://dl.dropbox.com/u/19871021/Database.mwb

FYI

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.

Answers to Neil McGuigan's questions.

*what does json field mean in social_account?* The json field in the social_account and social_messages serve dual purpose. First, it's a debugging aid, I don't know why I'd need it, but I'd rather have it and not need it than need it and not have it. Second, if I add new features to the app and use this field to enable that new feature to current users right away.
*what does activation_profile.date mean?* activation.profile.date is the date the activation code was generated, it expires after 7 days.
what about email attachments or facebook message attachments? I do have plans to handle attachments using Amazon S3, but I have some security questions I need answered first.
password for social account? Have you seen something like this when visiting a facebook app?

or this when adding a twitter app? enter image description here

same concept.

@Ron first, I'm using the BCrypt algorithm for the User table and AES for the email_account table, second, I used MySQL workbench to design the database and used an identifying 1:1 and 1:n relationship, is there anything I should have done differently?

asked	1 year ago
viewed	311 times

Please review my database design for an online organization tool

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

Browse other questions tagged mysql database-design or ask your own question.

Please review my database design for an online organization tool

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

Sign up or log in

Post as a guest

Browse other questions tagged mysql database-design or ask your own question.

Related