Groups, Users, and Inbox

database-design

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

recipients_inbox

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

Best Answer

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

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

TABLE inbox
    id number,
    user_id number,
    message_id number,
    etc...

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.

Related Question