Database schema for chat: private and group

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.

I think you may need to refine your domain model a little - without that, it's hard to say whether your schema is "right".

Taking Slack as a model (note - I haven't done a huge amount of research on this, so the details may be wrong), you might say that your system has "chats".

A chat can be public - i.e. listed for all users to see and join - or private - i.e. not listed for all users, and only available by invitation.

Public chats must have a "name" attribute. Private chats may or may not have a name attribute.

A chat can have 2..n participants.

All 1-1 chats start as private by default.

It is possible to change a private chat to a public chat.

In that case, you have an inheritance/specialisation relationship - "private" and "public" are subtypes of "chat".

The relational model is notoriously bad at dealing with inheritance; there are lots of related questions on SO.

I would do it more like this for a simple chat system with groups and privat chat (two member).

A other posibility is to create a table only for group message and one for privat chat. (to avoid the n:m between group and message table or you use the n:m like a feature and not as a posible bug / logic error). If you want a more complex chat system look at Neville Kuyt post.

I hope I was able to help you.