Sunday, September 18, 2016

Adjust DB structure to support filtering conversations and deleting them

I know this is not a specific GWT question, but I have an issue. I have a GWT application, which has a conversation system integrated within. To this conversation system, I have the following DB structure.

create table if not exists conversation(      id int(11) PRIMARY KEY AUTO_INCREMENT,      user_one varchar(25) NOT NULL,      user_two varchar(25) NOT NULL,      subject varchar(15) NOT NULL,      ip varchar(30) DEFAULT NULL,      date varchar(50) NOT NULL,      filter varchar(20) DEFAULT 'Inbox'  );    create table if not exists conversation_reply(      id int(11) PRIMARY KEY AUTO_INCREMENT,      reply text,      username varchar(25) NOT NULL,      ip varchar(30) DEFAULT NULL,      date varchar(50) NOT NULL,      conv_id_fk int(11) NOT NULL REFERENCES conversation(id)  );

I implemented a filtering mechanism where you can move conversation from Inbox to Archive or Important, or others. But when I execute the query which updates the filter:

update conversation set filter=? where id=?;

It will update this both to the user_one and user_two, since the GUI is the same to both users..there is no difference between user_one and user_two in terms of how the GUI looks like. So the select, which gets all conversations looks like this:

select * from conversation where (user_one=? or user_two=?) and filter=? order by id desc;

How should I change the DB structure in order to make the operations above (moving and deleting) work properly, because as I said, when I move as user_one, it will move to user_two as well, deleting the conversation from one would delete it from the other too.


Thanks in advance.

You received this message because you are subscribed to the Google Groups "GWT Users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
To post to this group, send email to
Visit this group at
For more options, visit

No comments:

Post a Comment