Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

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

share|improve this question

1 Answer

up vote 2 down vote

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.

share|improve this answer
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
@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. – Kerri Shotts Jun 18 '11 at 4:36
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. – Kerri Shotts Jun 18 '11 at 4:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.