design - SQL - Chat database schema to delete conversation for one side or both -


i designing chat database next requirements:

  • only private messages, b. no groups nor rooms.
  • when user send message b, if user deletes conversation, user b still able view conversation until user b deletes it.
  • messages not erasable individually. able delete full history conversation.

and have this:

  • when user send message user b, 1 message register created, id. foreign key conversation table.
  • in conversation table, 2 registers created same message id. 1 user sends message, , other user receives message. each register has field called in-out, specify if message send or received. example:

/*  conversation_table                              messages_table  +--------------------------------------------+  +----------------------------------------+  | user_id | participant_id | in-out | msg_id |  | msg_id |            body               |  +--------------------------------------------+  +----------------------------------------+  |       |        b       |    0   |   101  |  |   101  | hello b, what's            |  |    b    |              |    1   |   101  |  |   102  | hey a, here in stackoverflow  |  |    b    |              |    0   |   102  |  |   103  | that's nice b, , what's new |  |       |        b       |    1   |   102  |  +----------------------------------------+  |       |        b       |    0   |   103  |  |    b    |              |    1   |   103  |  +--------------------------------------------+      chat windows    +-----------------------------------------+  | user                                  |  +-----------------------------------------+  | sent: hello b, what's                |  | received: hey a, here in stackoverflow  |  | sent: that's nice b, , what's new     |  +-----------------------------------------+    +-----------------------------------------+  | user b                                  |  +-----------------------------------------+  | received: hello b, what's            |  | sent: hey a, here in stackoverflow      |  | received: that's nice b, , what's new |  +-----------------------------------------+    */

in way. able separate each individual user, full chat history, filtering required participant.

and separating send messages received messages results easy in-out var. example, if message received (0) put on left side, or if message sent, put right side.

sql messages user chatting user b:

select * conversation_table c inner join messages_table m on (c.msg_id=m.msg_id)   c.user_id=a , c.participant=b 

and insert messages user user b:

insert messages_table (msg_id, body) values (101, 'hello b, what's up')  insert conversation_table (user_id, participant_id, in-out, msg_id) values  (a, b, 0, 101) #messages out user user b (b, a, 1, 101) #message comes in user b user 

to delete message history user a, chatting user b:

first, check if user b had not deleted conversation. if have deleted, then, messages deleted messages table. otherwise, no.

delete conversation_table user_id=a , participant_id=b 

this delete full conversation between user , b, in user account. user b have it's own copy of messages.

messages table have meta data like:

  • timestamp (utc current miliseconds) date-time , order of visualization

well, working here, questions:

  • is presented design model handle thousands of users? mean, storing each user incoming , outgoing messages.
  • what message id. thinking in uuid 32 chars. advisable? (recommended). mean if message contains body "hello", 32 char unique id required, , think unnecessary, or not?
  • can me guide me in design?

thank you.


Comments

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

css - Make div keyboard-scrollable in jQuery Mobile? -

ruby on rails - Seeing duplicate requests handled with Unicorn -