Best database design for a discussion system with varying category types?

Question

I would like to design a message inbox table schema for messages which may belong to individual users or user groups.

Let's say we have these tables already:

• users table with uid (PK)
• groups table with gid (PK)
• groups_association table with gid (FK) and uid (FK)

Then, what would be the best design of the message and message-post tables to satisfy these requirements:

• any individual user can message another user
• any individual user can message multiple users
• any individual user can message ALL users in an individual group (the number of users within a particular group can vary over time)
• you should be able to query for all messages that belong to a specific user

My current thinking goes as this:

1. Set up three tables:
   • message with mid (PK) and let's say the subject
   • message_participants with mpmid (FK), mptype ENUM('user', 'group') and mpid (FK of the type)
   • message_posts with msgmid (FK) referencing the message table PK
2. To get all messages
   • get all groups for a particular user
   • query message_participants WHERE (mptype = 'user' AND mpid = uid) OR (mptype = 'group' AND mpid IN (all user groups))

This way you could even send messages to individual users and groups at the same time.

Is this how you would normally approach this type of problem?

Answer 1

This is a good question. Sounds like you are on the right track. Here would be my approach:

Tables:

• message with id, user_id, subject
• message_users with id (PK), message_id (FK), user_id (FK)
• message_groups with id (PK), message_id (FK)

Now for an example: Let's say a user sends a message to a group, you insert one row into message_groups, and you insert one row for each member of the group into message_users. This allows you to capture which group(s) were sent the message and which users received the message at that time. Users can be added and removed from groups in the past or future, so you must record each user who was in the group at the time the message was sent. You can't get away with just recording the group.

To get all messages for a user:

SELECT * FROM message INNER JOIN message_users ON message.id = message_users.message_id WHERE message_users.user_id = {user_id}

Also make sure you do not use a field that can reference more than one type of primary key. The mpid field in your message_participants table should not reference both users and groups.

Not sure why you needs posts. Do messages have multiple posts? Hope this helps.

Answer 2

James is correct in stating...

Also make sure you do not use a field that can reference more than one type of primary key. The mpid field in your message_participants table should not reference both users and groups.

Overloading a foreign key column is always a poor design that leads to poor data integrity and other problems.

If your goal is to have "transient recipients" -- i.e., if I join a group today, I instantly see all messages to that group from yesterday -- then your model is close. Describing a recipient as a "participants" is poor diction, but I would approach the problem as follows...

TABLE MessageRecipients
(
  Message_Id INT NOT NULL
    CONSTRAINT FK__MessagesRecipients__Messages
    FOREIGN KEY (Message_Id) REFERENCES Messages (Message_Id),

  RecipientType_Code CHAR(1) NOT NULL
    CONSTRAINT CK__MessageRecipients__RecipientType_Code_Domain
    CHECK RecipientType_Code IN ('U','G'),

  User_Id NULL
    CONSTRAINT FK__MessagesRecipients__Users
    FOREIGN KEY (User_Id) REFERENCES Users (User_Id),

  Group_Id NULL
    CONSTRAINT FK__MessagesRecipients__Groups
    FOREIGN KEY (Group_Id) REFERENCES Groups (Group_Id),

  CONSTRAINT CK__MessageRecipients__RecipientType_Validity
    CHECK (RecipientType_Code = 'U' AND User_Id IS NOT NULL AND Group_Id IS NULL)
       OR (RecipientType_Code = 'G' AND User_Id IS NULL AND Group_Id IS NOT NULL)

)

Otherwise, if you want messages to be tied to users regardless of group membership (i.e., If i drop a group, I still see messages from that group), then I would suggest James' approach.