Mail / Messaging schema question

I am creating a communication module with email and onsite mess开发者_运维百科aging. 4 questions:

1) When storing the messages, it can belong to folders (Spam, trash, inbox, outbox, etc). Are these separate tables or 1 table with just a column for "folder" which is FK to a "folder lookup table"?

2) Like on hotmail, a user can create x number of custom folders so how to represent that in the data model? I can say inbox = id 1, outbox = id 2, etc but for the custom folders like "vacation email", "work email", etc i am not sure how to show that in the Data model if a message resides in that folder.

3) One email goes to multiple people. Does this mean i need to have 1 row per user sent to?

4) Lastly, messages have attachments. i assume that means a separate attachments table which FK links to which ever table (s) are used for storing messages?

1&2: Folders need to be an entity, relationship MessageFolder one to many to Message

3: MessageUser entity with UserID, MessageID, Type (Sender,Recipient)

4: Seperate table for attachments (MessageAttachments).

CREATE TABLE `message`
  (
     `id`       INTEGER NOT NULL AUTO_INCREMENT,
     `author`   INTEGER NOT NULL,
     `contents` TEXT NOT NULL,
     `subject`  TEXT NOT NULL,
     PRIMARY KEY (`id`)
  );

CREATE TABLE `messagefolder`
  (
     `id`         INTEGER NOT NULL AUTO_INCREMENT,
     `id_message` INTEGER NOT NULL,
     `id_folder`  INTEGER NOT NULL,
     PRIMARY KEY (`id`)
  );

CREATE TABLE `folder`
  (
     `id`   INTEGER NOT NULL AUTO_INCREMENT,
     `name` VARCHAR(200) NOT NULL,
     PRIMARY KEY (`id`)
  );

CREATE TABLE `messageattachment`
  (
     `id`         INTEGER NOT NULL AUTO_INCREMENT,
     `fk_message` INTEGER NOT NULL,
     PRIMARY KEY (`id`)
  );

CREATE TABLE `user`
  (
     `id` INTEGER NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (`id`)
  );

CREATE TABLE `messageuser`
  (
     `id`         INTEGER NOT NULL AUTO_INCREMENT,
     `fk_message` INTEGER NOT NULL,
     `type`       INTEGER NOT NULL,
     PRIMARY KEY (`id`)
  );;

ALTER TABLE `message` ADD FOREIGN KEY (author) REFERENCES `user` (`id`);
ALTER TABLE `messagefolder` ADD FOREIGN KEY (id_message) REFERENCES `message` (`id`);
ALTER TABLE `messagefolder` ADD FOREIGN KEY (id_folder) REFERENCES `folder` (`id`);
ALTER TABLE `messageattachment` ADD FOREIGN KEY (fk_message) REFERENCES `message` (`id`);
ALTER TABLE `messageuser` ADD FOREIGN KEY (fk_message) REFERENCES `message` (`id`); 

1) When storing the messages, it can belong to folders (Spam, trash, inbox, outbox, etc). Are these separate tables or 1 table with just a column for "folder" which is FK to a "folder lookup table"?

One folder can have many messages. If a message can reside in one folder only, then the relations is one (folder) to many (messages), so yes to the 2nd choice. First choice is leads to a not normalized database.

2) Like on hotmail, a user can create x number of custom folders so how to represent that in the data model? I can say inbox = id 1, outbox = id 2, etc but for the custom folders like "vacation email", "work email", etc i am not sure how to show that in the Data model if a message resides in that folder.

Standars folders could be like like you said ... , sent = id 3, spam = id 4, trash = id 5. After that, any custom folder will have: vacation mail = id 6, etc...

No need for any change in the Data model.

3) One email goes to multiple people. Does this mean i need to have 1 row per user sent to?

One email, many recipients. So, yes, if you have a table for email-recipients relationship.

4) Lastly, messages have attachments. i assume that means a separate attachments table which FK links to which ever table (s) are used for storing messages?

Yes, like answer 3. (one email, many attachments).

If however, an attachment can be in many emails (like if one forwards an email and the attachment), and you want that in your model, the relationship will be many-to-many.