Help with DB/Search: Chat with multiple profiles

Hello folks!

I’m working on a project where I need to optimize the structure and search functionality for the following scenario:

  1. Multiple Profiles Per User: Each User can be associated with one or more Profiles.
  2. Profile-Based Chat System: Conversations (Chats) occur between these Profiles.
  3. Unified Chat View for Users: Users should be able to view all chats from all their Profiles on a single screen. Ideally, these chats would be organized by the time of the most recent message.

Current Design Approach:

  • Users and Profiles: Each User is linked to a list of Profiles.
  • Chats and Participants: Each Chat is associated with a list of Participants, where Participants are Profiles.

Challenge:

  • Aggregating Chats Across Profiles: My goal was to display Chats where the Participants list includes any Profile from a User’s list of Profiles. But, I’m facing difficulties in implementing this intersecting operation between the list of a User’s Profiles and the Participants in the Chats.

Any insights or alternative approaches are very welcome!

Thanks!

Hello again,

I’ve been working on an approach to address the challenge, and here’s what I’ve come up with so far (note: the User and Profile tables are simplified for this demonstration):

  1. Conversation Table:
  • I’ve introduced a “Helper Participant” column. Its purpose is to facilitate the quick identification of conversations between two Participants (if there is a conversation between two participants).
  1. Participant Table:
  • This table is specifically designed to enable searches for Conversations that involve any of the Profiles associated with a User’s Profile list. (the challenge itself)
  1. Message Handling:
  • Messages are linked to Conversations. Additionally, each message is tied to the Profile that sent it and the Profile designated to receive it.
  • I’ve added a “To” column to manage message visibility based on Privacy Rule settings, allowing certain messages to be hidden if needed.

I admit that this solution still feels a bit antiquated, but it’s the best layout I’ve managed to develop so far. I’m very open to any advice, insights, or questions that might help refine this approach.

Thanks!