Can two users share one table in messaging app?

[AC-3]

^{I couldn't find a better fit for this question than the programming section.}

I'm making a chat app for fun but have run into a problem I can't think myself out of.

The current schema would be [Users] -> [Chatrooms] -> [Messages] with foreign keys referencing back.

 

My current implementation would require the [Messages] and [Chatrooms] be stored in both users as [Chatrooms] has a foreign key to [Users], I'm using one-on-one chats only but would there be a smart way to store the [Chatrooms] and thus [Messages] only once and have both users refer into those tables?

 

It must be possible to not store the messages redundantly but maybe that isn't too smart and having the data tied with both users is the best way?

[Eigenvektor]

Not quite sure I understand, but you could make a message refer to a sender and recipient? This way a message could be bound to two users without having to store it twice.

 

messageId,
senderId,
recipientId,
chatroomId,
message

 

[Sauron]

8 hours ago, Oliver24x said:

^{I couldn't find a better fit for this question than the programming section.}

I'm making a chat app for fun but have run into a problem I can't think myself out of.

The current schema would be [Users] -> [Chatrooms] -> [Messages] with foreign keys referencing back.

 

My current implementation would require the [Messages] and [Chatrooms] be stored in both users as [Chatrooms] has a foreign key to [Users], I'm using one-on-one chats only but would there be a smart way to store the [Chatrooms] and thus [Messages] only once and have both users refer into those tables?

 

It must be possible to not store the messages redundantly but maybe that isn't too smart and having the data tied with both users is the best way?

One way of doing this would be to generate a dedicated key for that chatroom that is then shared between the users; a lot of systems rely on sharing a symmetrical key securely thanks to public keys. If I understand your question correctly then having a copy for each user so it can be separately encrypted with their key would also be a nightmare to sync properly.

[Franck]

If a chatroom is just a link between 2 or more users why would you need to store message twice ? message only need to be stored in that specific chat room table and have the userid of the sender and that's it.

 

the schema should be pretty much the following :

Table : User

Data : info about the user

 

Table : [UserId]_Chatrooms

Data : List of chatroom the user is part of

 

Table : Chatroom_[ChatroomId]

Data : list of messages in that room

[Nayr438]

I would do something along the Lines of this

Login
    - AID (Account ID INT - Auto)
    - UserID (Login ID)
    - Password
    
UserInfo
    - AID
    - DisplayName
    
Rooms
    - RID (Room ID INT - Auto)
    - DisplayName
    
RoomUsers
    - RID
    - AID
    
Messages
    - RID
    - AID
    - TimeStamp
    - Message