The best database design for a discussion system with different types of categories?
I would like to create an inbound table schema for messages that can belong to individual users or user groups.
Let's say we already have these tables:
- user table with uid (PK)
- group table with gid (PK)
- groups_association table with gid (FK) and uid (FK)
Then what would be the best design of messages and message tables and messages to meet these requirements:
- any user can tell another user
- any user can report to multiple users
- any user can report to all users in a particular group (the number of users in a particular group may change over time)
- you should be able to query all messages belonging to a specific user
My current thinking goes like this:
- Set up three tables:
- with the middle (PK) and let him say the topic
- message_participants with mpmid (FK), mptype ENUM ('user', 'group') and mpid (FK of type)
- message_posts with msgmid (FK) referencing the PK message table
- Get all messages
- get all groups for a specific user
- query message_participants WHERE (mptype = 'user' AND mpid = uid) OR (mptype = 'group' AND mpid IN (all user groups))
This way, you can even send messages to individual users and groups at the same time.
How do you usually approach this problem?
source to share
James is right in stating ...
Also make sure you are not using a field that can reference multiple primary key types. The mpid field in the message_participants table must not refer to either users or groups.
Foreign key column overloading is always bad design, resulting in poor data integrity and other problems.
If your goal is to have "transient recipients" - that is, if I join a group today, I immediately see all the messages from that group from yesterday - then your model is close. Describing the recipient as "participants" is bad diction, but I would approach the problem like this ...
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 posts to be bound to users regardless of their membership (i.e. if I delete a group, I still see posts from that group), then I would suggest James's approach.
source to share
That's a good question. It looks like you are on the right track. Here's 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 is sending a message to a group, you insert one line in message_groups and insert one line for each group member in message_users. This allows you to keep track of which group (s) the message was sent to and which users received the message at that time. Users can be added and removed from groups in the past or in the future, so you must record every user who was in the group at the time of posting. You cannot leave simply by signing up a group.
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 are not using a field that can reference multiple primary key types. The mpid field in the message_participants table must not refer to either users or groups.
Not sure why you need messages. Do posts have multiple posts? Hope this helps.
source to share