开发者

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

开发者 https://www.devze.com 2023-01-29 15:37 出处:网络
I have a simple table like this in mind to create a db base email system, is this the best approach ? TABLE `message`

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

精彩评论

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