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
精彩评论