CODEWITHSUNDEEP
mysqldatabase
George
George

Reputation: 1114

Personal and Global messaging database design

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.

Upvotes: 1

Views: 252

Answers (1)

Linger
Linger

Reputation: 15048

Why not combine the message_status table and the recipient table? Then you only update/insert one table instead of two.

recipient:

      |r_id| m_id | recipient_id|  status | deleted |
      | 50 | 100  |  02         |  read   |  no     |
      | 51 | 200  |  01         |  unread |  no     |
      | 52 | 300  |  01         |  read   |  yes    |
      | 53 | 300  |  02         |  unread |  no     |

Upvotes: 1

Related Questions