Database schema for chat: private and group

Question

I'm trying to design the database schema with the ability to both private chat and group chat. Here's what I've got so far:

So - the theory is that even if the user is just in a one on one private chat, they are still assigned a 'roomID', and each message they send is to that room.
To find out all the rooms they are involved in, I can SELECT a list from the table participants to find out.

This is okay, However it feels to me that the room table is slightly redundant, in that I don't really need a room name, and I could leave it out and simply use the participants table and SELECT DISTINCT roomID FROM particpants to find out the individual rooms.

Can anyone explain to me a better structure or why I should keep the room table at all?

Answer 1

Your schema looks perfectly fine, you might see the others (including myself today) came with more or less the same structure before (Storing messages of different chats in a single database table, Database schema for one-to-one and group chat, Creating a threaded private messaging system like facebook and gmail). I'd really like to note that your visual representation is the best of all, it's so easy to understand and follow :)

In general, I think having "room" ("chat", "conversation") makes sense even if you have no specific properties at the moment (as it might be name, posting_allowed, type (i.e. if you reuse the similar structure not only for private messages and chats but i.e. to public posts with comments) and so on. Single table with the single index ID should be super fast and have close to zero overhead, however it will allow extension quite easily without need to modify all existing code (i.e. one day you decide to add a name to chats). Keeping the roomID logic "hidden" inside participants table will not be transparent and neither efficient (i.e. when you need to find next ID of the chat), I wouldn't recommend that.