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.

Answer 2

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.

Answer 3

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.

Answer 4

I know this is a little late in the game but I've made a few of these and I always have an active type bool col in the message table. Just incase someone says something you can hide it but still keep a record of it. As well as user_auth in the users table. Sometimes I put in the room table auth_required -> user.user_auth incase you want leveled conversations like in many discords and always a datetime in the message col. Those are the standards at min because you will regret later if you don't have them..