Mysql – Personal and Global messaging database design

database-designMySQL

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.

Best Answer

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

Related Question