yoosuf/messenger

Messenger Database Design Concept

Messenger

Messenger Database Design Concept for chat applications, by using this, you will be able to create group chat or a one to one chat application without a hassle.

If you really like it? use it, implement it and share the link with me ;) Happy Messaging!

Startup? want to build an app?

If you have a query about building an app or an API for your Mobile application, please consult at [email protected] or visit Crew, Crew is an open innovation house based in Sri Lanka.

Versions

1.0.3

Attachment table added, now you can add galleries for messages

1.0.2

#4 fix

1.0.1

Updated Access to devices relationship as one to one.

1.0

Initial commit.

messenger's Issues

microservices?

hello!

it's awesome database architecture, but.. what about microservices? :3 highly coupled tables there, how to separate them?

Messages message_type TYPO

I found a typo in messenger.sql :
message_type ENUM('text', 'image', 'vedio', 'audio') NOT NULL,

participants_id in messages

I am confused about two things in the deisgn

1. Why have you included participants_id in messages table? If a message belongs to conversation then the participants can be know from it.
2. why have you made the type(single, group) in participants table instead of conversation?

I found some weird properties inside your Schema

Can you please create a Docs about these things so that other people can understand easily?

messages table

• guid
• sender_id:

• sender_id: user id of the person sending out the chat

• attachment_thumb_url
• attachment_url
  **

Why are there no FOREIGN KEYs in messenger.sql?

Why are there no FOREIGN KEYs in messenger.sql?

Add Ad-hoc queries

It would be great if you can add sql queries like Recent messages, conversations.

Can i send a message from person to person

I can't understand the database correctly.
Can I send a message from person to person I don't see this in this database.
Is there an explanation of the database?

What is types column in participants table?

Can you explain types column in participants table using for what? Can you list enum values of this. Thanks

Create new Feature for this database

I see your database schema is really great and I think it will help me a lot to build a Chat App. However, I still want to custom it for some of my own ideas. So basically, right now I just want to do this thing like this.

• There are lots of Topics in my App like Friends, Family, ... (So each Topic will have ID, Name as well)
• So when they join into my Chat Room, I show for them lots of those Topics
• After they hit to join talking about one Topic, I show for them lots of other users in that Topic Room
• In that Topic room, they can choose any people to talk with (just one for now)
• For now, I just support for them chat between one user and one user.

I'm still lack of my knowledge about building this thing so it will be good if someone can help a while with this cool idea.
Thanks a lot for your time.

Adds support for unread counts and ordering strategy

Would be nice to document the unread count and ordering query strategies in the repo.

What is "guid" column in messages table?

Can you explain guid column in the message table using for what?

delete conversation

imagine if one user deletes a conversation, he would lose all the messages of it ofcourse, but later he start a new conversation (sends or receives a new msg ) with that same user he just deleted the previous conversation.
My question is how to deal with this operation in the table conversation?

Participants table: Add unique index on conversation_id and user_id

We could add a UNIQUE constraint to avoid adding the same user on the same conversation multiple times. Let me know what you think.

Happy to provide a PR if you agree.

Should we define `type` in `conversation` table instead of `participants`?

    @yoosuf Should we define this type in **Conversation** table. I mean when a user chat 1 x 1 (type = single) and when he/she chats on a group (type = group), **type** should be define in **Conversation**, right ?

Originally posted by @trankhacvy in #10 (comment)

I have the same question with this guy.
In current version, we can encounter the case that two (or more) records have a same conversation_id but different type. So how can we avoid it?

Identifying duplicate conversations

Interesting db design. I'm curious though, do you create a conversation.id once, say user A selects user B to have a conversation with? Or do you only create the conversation.id once a message is exchanged between the two?

Selection Queries

Hi there, i was wondering where are your selection queries?