Please tell me how it is done correctly.

There are 2 users who can exchange messages and delete messages from visibility on their page.

I have the following structure:

id user to_user message date

but it is also necessary that the user can delete messages from the chat, but the essence of the message itself remains.

I think this is done with 2 fields with boolean values, which would entail unnecessary queries. user to_user in essence there is no dialogue, there are only messages that connect and form a dialogue, which means there are 2 options: user to_user and to_user user .

** it is necessary that the user can delete the message, thereby the message becomes invisible for this user. but it is visible to his interlocutor.

In general, so as not to fence the city I appeal to you for advice)

Thank.

  • I would rather not put a table with chat messages in mysql , since it will be too fat for her when sampling. Or play with the types of tables and scaling. To implement soft deletion, make the is_removed field, most frameworks contain ready-made behaviors for working with soft deletion. - DanielOlivo
  • I do not understand ochem exactly what you say. to be honest :( - mydls1
  • it is necessary that the user can delete messages from the chat in general for all or only for themselves? in the first case, you can do without add. fields, just change the sign user_id . - Akina
  • 2
    Change user_id sign - beyond good and evil - vp_arth

1 answer 1

Basic chat package for any number of people:

 Conversations (id) Participants (conv_id, user_id) Messages (id, conv_id, user_id, message)

If limiting the number of people talking to two is iron, remove the Participants table, add the user_1 , user_2 to Conversations . However, the query will appear terrible constructions like:

 (c.user1=:a AND c.user2=:b OR c.user2=:a AND c.user1=:b)

It may make sense not to prohibit the existence of conferences at the base level, just do not create them ( until one day you enter the "Add buddy" button )

The possibility of one-sided deletion of messages

To do this, you need to store somewhere a log of the unreleased messages of each user:

 UserMessages (user_id, message_id)

From which we will delete the lines.