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.

  • 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 '14 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 '14 at 12:51
1

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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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