开发者

what would be a good db design for database base email system?

I have a simple table like this in mind to create a db base email system, is this the best approach ?

TABLE `message`
      - id
      - parent_id
      - message
      - subject
      - created_on
      - is_draft
      - sender_profile_id      


TABLE `email_message`
      - id
      - is_read
      - is_deleted
      - message_id
      - profile_id

case 1: profile A sending email to profile B, and B Replies back (one to one communication)

INSERT INTO `message` (`id`, `parent_id`, `message`, `subject`, `created_on`, `is_draft`, `sender_profile_id`) VALUES
(1, 0, 'Hi what''s up how are u', 'Hi', '2010-12-08 11:27:54',开发者_Go百科 0, 1),
(2, 1, 'yeah i am gud', 0, '2010-12-08 11:28:19', 0, 2);

INSERT INTO `email_message` (`id`, `is_read`, `is_deleted`, `message_id`, `profile_id`) VALUES
(1, 1, 0, 1, 2),
(2, 1, 0, 2, 1);

case 2:

-Profile A sending email to profile B,C, D.

-Profile B repling back all to whole group.

-A replying again to whole group.

-C replies to A only

INSERT INTO `message` (`id`, `parent_id`, `message`, `subject`, `created_on`, `is_draft`, `receiver_profile_id`) VALUES
(3, 0, 'Hi what''s up how are u', 'Hi', '2010-12-08 11:27:54', 0, 1),
(4, 3, 'yeah i am gud.', 0, '2010-12-08 11:28:19', 0, 2),
(5, 3, 'why are u gud?', 0, '2010-12-08 11:28:19', 0, 1),
(6, 3, 'what?', 0, '2010-12-08 11:28:19', 0, 3);

INSERT INTO `email_message` (`id`, `is_read`, `is_deleted`, `message_id`, `profile_id`) VALUES
(3, 1, 0, 3, 2),
(4, 0, 0, 3, 3),
(5, 0, 0, 3, 4),        

(6, 0, 0, 4, 1),
(7, 0, 0, 4, 3),
(8, 0, 0, 4, 4),

(3, 0, 0, 5, 2),
(4, 0, 0, 5, 3),
(5, 0, 0, 5, 4),

(6, 0, 0, 6, 1);


I feel like a single table would be a lot easier to impliment :

TABLE Message - MessageId (GUID) - ParentId (GUID) - Subject - Message - To - Sender - CreatedOn - isDraft - isDeleted - isRead

If you are sending an email to a group, just create multiple records with different "To" entries

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜