Android Question A chat conversation list table database

You should identify conversations

ID (Autoincrement)
ID_Conv
User (ID or Name of the writer)
Message:
Time: (when it was sent)
Reading: (Map Type) List of who read the message

You do not need much else for the conversation.
BUT you may have another table where you store the participants

ID_Conv: (Auto_increment)
Participants (Map Type) List of participants

I take a look at conversation structure here on b4a and find out that the conversation has it own subject or ID_conv
You want to start the discution again with a member you must search for that particular subject for conversation

unlike facebook_mesenger or Whats'up where discution from one to another is found in the same feed , there is not subject of discution this subject is more likely to be the name for a group
user clicks the users lists ...and mate to a unique conversation ID

I see two diferent types
- private conversation with an unique ID with two participants
- group conversation with an unique ID with multiple participants

I am somehow stucked ....because each one how enters the members list clicks the other and switch the id ....User (ID or Name of the writer) ...as you said

??

problem is that member A AND B can have two diferent ids
234 AB
564 BA

Click to expand...

To solve this problem alone, you could order users' IDs from lesser to greater so whenerver A and B would initiate a chat, their relative order will be always the same and you would end up with a single specific record.

First time the user clicks the user from members list

I expect this to return all records from combination
10, 23
23, 10
where 10,23 are ids for the two members from the private conversation

The sql query result in a list of results
list size =0 - (if succesful no one initiated the conversation yet (Create new conversation)

list size =1 - Enter the conversation from with the returned conversation ID(composed with the two members id as composed Key) (open existing conversation)

I guess who opens the conversation does not mather .
I am somehow new to my sql , maybe there are other methods that is what I am thinking right now.


The table is not well build though:
I think autoincrement ID must be remouved
I think that better I would create a Primary composed key from the two ID
id_m1 , Id_m2 as PRIMARY ....no no entry could have a duplicate as the members id are unique as well