How to organize database tables for messaging system when messages can be broadcasted?

Imagine an e-commerce website where users can create their own stores.

I'm designing internal messaging system for such website, and I'm currently hesitant as to how to implement it database-wise.

The following scenarios need to be supported:

• [u => u] user can send message to another user;
• [u => s] user can send message to a store;
• [s => u] store can send a message to a user;
• [s => uu] store can send a message to all its customers.

If I only had a simple Messages table, and a store wanted to broadcast a message to 10,000 users, I would need to insert 10,000 similar records at once.

Another approach is to keep PersonalMessages and BroadcastMessages separate, and introduce BroadcastMessageReceivers "mapping" table. However, this approach complicates working with individual messages, like deleting, showing the inbox and outbox, etc.

Is there any recommended optio开发者_Python百科n, considering I'm using Entity Framework and MS SQL 2008?

---

If you want each user to be able to manage content of his inbox independently you must handle broadcast message as set of separate messages (or some M:N relations) - each user must have his own record so if he deletes the message from the inbox other users will not be affected. The implementation depends on the way how you want to handle broadcast - do you need to work with broadcast message itself? Then you need separate entity type.

If you want to use Entity framework make sure that you have stored procedures for broadcasting. Stored procedure will take the message, sender and the store and and create records for all target users. Doing this through EF directly will be terribly slow.

---

I will do it this way,

Message Table
   ID
   FromUserID int FK
   ToUserID nullable int FK
   Title
   MessageBody

Here when ToUserID is null, then message is for everyone. Otherwise it's private for ToUserID. Queries are simple too,

To display all message for one user and broadcasted users you can use,

Context.Messages.Where(
       x=> x.ToUserID == null ||
           x.ToUserID == currentUserID
    );

In this approach, you will not be storing multiple messages for broadcast. But if you want user to delete this message then you have no choice but to insert all records for every user.