1

I am trying to implement a database design system that will be sending messages to a person(thus 1 to 1) or many persons (1 to many). 

    table --users
              |u_id | Name |
              | 01  | Aku  |
              | 02  | Sika |
              | 03  | Admin|

    table --messages 
              |m_id | sender_id | Body   | Time |
              | 100 |  01       | hello  | 16:04|
              | 200 |  02       | Hi     | 16:08|
              | 300 |  03       | hi Guys| 19:12|

    table --recipient
              |r_id| m_id | recipient_id| 
              | 50 | 100  |  02         |
              | 51 | 200  |  01         |
              | 52 | 300  |  01         |
              | 53 | 300  |  02         |

    table --message_status
              |m_id | recipient_id | status | deleted |
              |100  |   02         | read   |  no     |
              |200  |   01         | unread |  no     |
              |300  |   01         | read   |  yes    |
              |300  |   02         | unread |  no     |

From my schema above, a user can send a message to one person and he can also send a message to many persons.Once a user sends a message to a person, we will have all the recipients userid in the recipient table as well as in the message_status table . So assuming a user is sending one message to 1 million users at a time, it means, there will be a 1 million rows in both the recipient and message_status table.

Please can there be a way to handle this by reducing the many insertion of rows when a user sends a message to many users or that is the right thing to do.

NOTE

Please the system should allow every recipient to delete a recieved message if he/she so wishes, however their delete should not affect other recipients. So if user with u_id = 02 deletes her recieved message, user with u_id=01 should still view his message.

Thanks for helping.

Improve this question
2
  • 1
    It would seem that the relationship between persons and messages is really many-to-many. If so, you should make that clearer in your question.
    – Walter Mitty
    Jul 4, 2014 at 15:58
  • You should see my answer to this question on Stack Overflow, where I go into quite a bit of detail for essentially the same requirement: stackoverflow.com/questions/6541302/…
    – Joel Brown
    Jul 5, 2014 at 12:51

1 Answer 1

Reset to default
1

You could combine recipient and message_status. That would halve the number of inserts.

Improve this answer
1
  • 1
    Yea thanks,but that still means that i would still be inserting one message Id for say 1 million recipients
    – George
    Jul 6, 2014 at 16:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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