Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm building a site that implements David Allen's Getting Things Done that pulls in your email, Facebook newsfeed, tweets from those you follow on Twitter, and more services are planned. The problem is that I'm not a DBA, and I'm not sure how to design the database so that as I add features to the site, I won't have to artificially corrupt people's raw data for the purposes of storing it (for example, I want to add the ability to get RSS feeds sometime in the future, but I'm not sure how I'd do that without making a mess).

I've put down my initial ideas using DBDesigner 4, below, you'll find the diagram and the SQL.

A few notes to help clarify clarify things.

  • The Accounts table is for storing authentication tokens and such for facebook, twitter, and such.
  • The messages table is incomplete.
  • The password fields in emailconfiguration and users are encrypted, users with a one-way hash, emailconfiguration with a two-way.
  • I'm using a MySQL database using the InnoDB storage engine on Amazon RDS.
  • Each project may have one context associated with it.
  • Each message may have a project and context, but it's not required.
  • The imap, smtp, and pop3 tables exist to remove duplication within email configuration.
  • queries to this database are generated by Korma, a clojure library.

Can someone please point me in the right direction? I'd also be willing to look at using a NoSQL database if suggested. Thank you for your time and consideration.

site database schema

Here's the SQL create script just in case anyone wants to see it.

CREATE TABLE Pop3 (
  domain VARCHAR NOT NULL,
  host VARCHAR NULL,
  port INTEGER UNSIGNED NULL,
  ssl BOOL NULL,
  PRIMARY KEY(domain)
)
TYPE=InnoDB;

CREATE TABLE Imap (
  domain VARCHAR NOT NULL,
  Host VARCHAR NULL,
  port INTEGER UNSIGNED NULL,
  ssl BOOL NULL,
  PRIMARY KEY(domain)
)
TYPE=InnoDB;

CREATE TABLE users (
  Username VARCHAR NOT NULL AUTO_INCREMENT,
  email VARCHAR NULL,
  password_2 VARCHAR NULL,
  activation VARCHAR NULL,
  is_active BOOL NULL,
  PRIMARY KEY(Username)
)
TYPE=InnoDB;

CREATE TABLE smtp (
  domain VARCHAR NOT NULL,
  host VARCHAR NULL,
  port INTEGER UNSIGNED NULL,
  ssl BOOL NULL,
  PRIMARY KEY(domain)
)
TYPE=InnoDB;

CREATE TABLE projects (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  users_Username VARCHAR NOT NULL,
  name VARCHAR NULL,
  description TEXT NULL,
  context INTEGER UNSIGNED NULL,
  PRIMARY KEY(id, users_Username),
  INDEX projects_FKIndex1(users_Username),
  FOREIGN KEY(users_Username)
    REFERENCES users(Username)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

-- ------------------------------------------------------------
-- This is the table where access info for facebook, twitter, and others is stored.
-- ------------------------------------------------------------

CREATE TABLE Accountsi (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  users_Username VARCHAR NOT NULL,
  owner INTEGER UNSIGNED NULL,
  service VARCHAR NULL,
  username VARCHAR NULL,
  send INTEGER UNSIGNED NULL,
  receive INTEGER UNSIGNED NULL,
  info TEXT NULL,
  PRIMARY KEY(id, users_Username),
  INDEX Accountsi_FKIndex1(users_Username),
  FOREIGN KEY(users_Username)
    REFERENCES users(Username)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE EmailConfiguration (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  users_Username VARCHAR NOT NULL,
  owner INTEGER UNSIGNED NOT NULL,
  address VARCHAR NULL,
  psswd VARCHAR BINARY NULL,
  domain VARCHAR NULL,
  PRIMARY KEY(id, users_Username),
  INDEX EmailConfiguration_FKIndex1(users_Username),
  FOREIGN KEY(users_Username)
    REFERENCES users(Username)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE Messages (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  users_Username VARCHAR NOT NULL,
  message_id VARCHAR NULL,
  user_id VARCHAR NULL,
  account INTEGER UNSIGNED NULL,
  service VARCHAR NULL,
  project INTEGER UNSIGNED NOT NULL,
  context INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY(id, users_Username),
  INDEX Messages_FKIndex1(users_Username),
  FOREIGN KEY(users_Username)
    REFERENCES users(Username)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;

CREATE TABLE context (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  projects_id INTEGER UNSIGNED NOT NULL,
  projects_users_Username VARCHAR NOT NULL,
  users_Username VARCHAR NOT NULL,
  name VARCHAR NULL,
  description TEXT NULL,
  PRIMARY KEY(id, projects_id, projects_users_Username, users_Username),
  INDEX context_FKIndex1(projects_id, projects_users_Username),
  INDEX context_FKIndex2(users_Username),
  FOREIGN KEY(projects_id, projects_users_Username)
    REFERENCES projects(id, users_Username)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(users_Username)
    REFERENCES users(Username)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION
)
TYPE=InnoDB;
share|improve this question
6  
You don't really store the Social Networks (Twitter, Facebook, etc) access info into your app data. Check their API. You have to work with their APIs to grab their data and wrap them on ORM objects or something. Try stackoverflow.com for this question :-) –  MarlonRibunal Apr 4 '12 at 22:24
3  
You could have waited a couple more days and come to chat to talk about this Q. There was no need to put a bounty on it in the first three days of asking it. –  jcolebrand Apr 5 '12 at 3:53
    
So is your question just, "Is my design good?" Do you have a specific concern? –  Nick Chammas Apr 9 '12 at 20:54
4  
If you aren't a database designer, why don't you hire one? Scalable databases take expertise. Getting that expertise takes about ten years. –  HLGEM May 16 '12 at 15:24
    
Looks like stackoverflow, not DBA –  David Aldridge Jun 22 '13 at 0:05
add comment

1 Answer 1

Is there a reason why you have no unique constraints in your database? What happens if somehow you get two rows with configuration data for the same email address? How do you determine which one to use?

I think the one piece of real advice I can give you is look at keys/functional dependencies and add as many unique constraints as make sense. it is far easier to drop unique constraints later than it is to add them.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.