37

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:

enter image description here

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?

Chud37
  • 4,907
  • 13
  • 64
  • 116
  • do you have foreign keys? – Ryan Gadsdon Sep 29 '17 at 08:45
  • @RyanGadsdon yes the lines indicate foreign keys – Chud37 Sep 29 '17 at 09:05
  • Ah i see, used to seeing FK next to name :) i would separate private and public rooms. Or create Room attribute with private and public room sub class. I would keep room because what happens if you had different chat rooms? e.g sport, social, work. It would be easier to link people to chat rooms – Ryan Gadsdon Sep 29 '17 at 09:08
  • @RyanGadsdon What to name the group for one to one chat. They should see each other's name. – sujeet Jun 27 '20 at 03:10
  • One solution that I think could using both user's username like `john_mike` and split them while sending for any of one. For `mike` show john and for john show `mike`? – sujeet Jun 27 '20 at 03:19
  • Could the room_Id and the user_id from the message table be removed and have instead a participant_id to be more efficeint? – Miguel Cardona Polo Jun 06 '21 at 11:01

5 Answers5

16

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.

The Godfather
  • 4,235
  • 4
  • 39
  • 61
  • What to name the group for one to one chat. They should see each other's name. – sujeet Jun 27 '20 at 03:10
  • One solution that I think could using both user's username like `john_mike` and split them while sending for any of one. For `mike` show john and for john show `mike`? – sujeet Jun 27 '20 at 03:19
  • @SujeetAgrahari you might want to give users opportunity to name their chats in whatever way they want (probably, separated `chat_names` table will be required with key (chat_id, user_id)). If there is no specific name (i.e. you want to show something default), then just keep `name` value empty/null in the DB and generate the name dynamically on the client (as you said, if it's 1:1 chat, then show partner's name, for example). *Don't store magical string which you're going to split on the client side* – The Godfather Jun 28 '20 at 10:32
  • 1
    "if it's 1:1 chat, then show partner's name" this is the problem. A should see B, and B should A, like in other chat apps. I see your name and you see mine in your chat window. How I would I implement that with this schema? And also for group chats let's say A, B and C are involved. A should see "B, C", and B should see "A, C" in their chat window. – sujeet Jun 28 '20 at 15:16
  • 3
    @SujeetAgrahari all this should be done on the client side IMO. DB structure allows you to get all participants of the given chat (`SELECT userID FROM participants WHERE roomID=123`) and then just process the participants in your code, not in the database. – The Godfather Jun 29 '20 at 13:41
  • I have done the same. I first fetched a user's all the rooms, and then again fetched all the user's in those rooms except this user. Then I group them. I was just curios if there is any better way, thanks. – sujeet Jun 30 '20 at 03:16
  • I still finding the best solution for for display room name when the room is 1-1, A should see B and B should seed A vice versa – Marosdee Uma Aug 13 '23 at 01:05
1

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.

Neville Kuyt
  • 29,247
  • 1
  • 37
  • 52
1

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..

MICHAEL MASTERS
  • 49
  • 4
0

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

erm

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.

SCHREDDO
  • 41
  • 5
  • 1
    You know, right, that traditionally N:M relations are done via intermediate table? :) So user-group(room) is exactly `participants` table from OP picture. Message:group is 1:N relation same as message:post_by_user. So I see no difference with your answer and initial OP's picture apart of the fact that OP's scheme is much easier to understand – The Godfather May 16 '20 at 13:15
-3

Database Schema for Private Chat and Group Chat

click hear show table schema

BDL
  • 21,052
  • 22
  • 49
  • 55
Rahul Root
  • 1
  • 3
  • 1
    While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/low-quality-posts/34694096) – XMehdi01 Jul 18 '23 at 08:49
  • While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/late-answers/34693914) – Yogendra Jul 19 '23 at 08:23
  • @XMehdi01: The "link" is an image on Stack Overflows image provider. Op's reputation isn't high enough to embed the image themselves. Please check link targets before voting to delete. – BDL Jul 19 '23 at 13:19
  • @Yogendra: The "link" is an image on Stack Overflows image provider. Op's reputation isn't high enough to embed the image themselves. Please check link targets before voting to delete. – BDL Jul 19 '23 at 13:20
  • @BDL You need to learn https://stackoverflow.com/help/how-to-answer . This is a community base standard. So everyone follow the community standard while review. It's not my personal view. – Yogendra Jul 20 '23 at 04:43
  • It's not really clear how is your picture different from what was already suggested. Answers without any text are not really helpful. Particularly, I struggle to understand what are those fields in blue, if those are Foreign Keys, how can they be "timestamp" and what is "_1" there? You might have got way better attitude if all of this was explained in your answer from the very beginning. – The Godfather Aug 23 '23 at 15:25