Android Question A chat conversation list table database

[hookshy]

How do you make a private conversation list table in a database ?

This is how I think I will do this
I will create a new table named chat with

1.id autoincrement (KEY PRIMARY)
2.id_m1 - the identity of first participant from members table (KEY PRIMARY)
3.id_m2 - the identity of second participant from members table (KEY PRIMARY)
4. created_at - row timestamp
....

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

It might be a bit confusing , I could try to search for the Table before to get the autoincrement id if the conversation started allready and initiated from user A ?

How did you do it ? Is my practice corect ?
Thanks

 

[Star-Dust]

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

 

[hookshy]

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

??

 

[udg]

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

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.

 

[hookshy]

First time the user clicks the user from members list

SELECT * FROM `c` WHERE c.id_m1=10  and `c`.`id_m2`=23
UNION
SELECT * FROM `c` WHERE c.id_m1=23  and `c`.`id_m2`=10

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