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
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.
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.