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.