We're working on a PM system where a user can send an email to other users or groups.

We currently have the following tables:

  • Users
  • Groups

Users can be part of many groups. A user doesn't have to be part of any group.

The problem I am having is with managing recipients and inboxes. Groups don't have inboxes - users do. An inbox stores information like whether or not the recipient has read the message, marked it for follow up, deleted it, etc.

So I made two tables:



recipients just stores the thread id, recipient id, and whether or not the recipient is a group or not.

recipients_inbox has the users who this message was sent to - whether as part of a group or individually. It stores: thread id, recipient id, when the thread was last read, flagged, etc.

My question is: Does it make sense to keep the recipients and recipients_inbox table separate? Should I just combine them and where the recipient is a group leave user-fields such as lastRead and flagged as null?

Thank you


Here's how I think I would approach the issue:

TABLE message
    id number,
    message varchar2(8000),
    sent_by_user number,

TABLE inbox
    id number,
    user_id number,
    message_id number,

Then, when sending to a group, I would go ahead and add an entry to each user's inbox with a pointer at the message. This way you aren't duplicating the message content, but you simplify the system by allowing users to perform their own actions on group messages without affecting the group message for the group as a whole.

Not sure it is a complete answer to your issue, but I would go this route since it means that message management is always a user-level issue; only during send operations do groups need to enter the equation.

  • I already have a message table, I just didn't include it in the details - didn't think it was relevant. In that table we store the the content and information like whether or not you can respond to that message. We can't include for_user number and for_group number as the message can eb sent to multiple people. Your answer really doesn't address my question of what to do with groups, users, and an inbox.
    – Swati
    Jun 17 '11 at 20:08
  • 1
    @Swati - I'll argue that it does solve a good portion of your problems. Here's why: The inbox table is a relation between your users and the messages they've received. When a group is the recipient, your send routine splits the group up into users and duplicates the relation across the entire population. What it doesn't do is duplicate the message content. Therefore all your code for handling messages (marking as read, reply, etc.) is exactly the same regardless of if a user or group received the message. Jun 18 '11 at 4:36
  • 1
    Further, the only difference in users vs. groups is in your send routine; all other times groups technically don't factor in to your equation. Yes, the inbox relation is duplicated across the group, but it has to be anyway for individual users to act upon messages sent to a group. (For example, if I mark a message as read, you don't want that action marking the message marked as read for the entire group.) So I think making groups a secondary level that splits during a send operation makes more sense than building lots of code to handle groups throughout. Jun 18 '11 at 4:39

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.