I have two tables in my database: 'customers' and 'store_owners'. These tables have a many-to-many relationship. I want to create a chat feature between the 'store_owner' and their customers. However, I encountered a problem when I created a 'chat' table in my database. This table includes 'IdSender', 'IdReceiver', and 'message'. Both 'IdSender' and 'IdReceiver' are foreign keys from the previously mentioned tables. However, in the same conversation, a customer can be both a sender and a receiver, and the same applies to the store owner. What should I do? Please note that the 'customers' and 'store_owners' tables must remain separate. I am using React Native and Laravel.
I attempted to create two foreign keys for the same ID, but as expected, it did not work.
In the structure you describe there are no unique senders and receivers, as that would be a one way chat, instead there only chats, chat members and messages.
So create a chats table, create a chat_members (chat_id, user_id) pivot table, assuming both customers and store_owners relate to a user, if not use a morph many relation, linking the chat to however many users are in your chat. And a chat_messages (chat_id, sender_id, message) table. Whenever a message in sent to a chat, all members of that chat should receive the message.