It is necessary to make a list of conversations between users like VKontakte. There are two tables for messages in the dialog between users using this query "SELECT * FROM inbox WHERE user='$login' AND author='$sobesednik' UNION ALL SELECT * FROM outbox WHERE user='$sobesednik' AND author='$login' ORDER BY time" will there be conflicts between the tables? the names of the fields in the tables are the same if you can rename the names of the fields to inbox_user, inbox_author and outbox _user, outbox _author. So far I am coping only with simple sql queries ... Thanks in advance.

  • And why do you need 2 different tables. After all, you have to duplicate each message in the inbox of the one to whom they write and the outbox of the one who writes. better to keep one table and in it fields of the sender and the receiver - Mike
  • in order that if I cleared the dialogue with the user, he would not erase the messages that I sent him - Den
  • for dialogs, it is worth thinking about using a separate room table or room_id field, simplifying the task while increasing the base - labris
  • @Den Well, this is solved simply by a field in which it is marked that the record has been deleted. It is possible by two bits, for the sender and the receiver, it is possible as. But storing two copies for sure is not why - Mike

1 answer 1

Bad advice

When there is the same data, but they need to be shown to different users or divided by access levels - create tables for each access level / user and write data there. How many access levels / users - so many tables! It's simple :)

... and then suffer with queries and other issues .. And now in the case ...

Introduction to the problem

Do you think there will be only conflicts? Leave this architecture ... wait for a while and there will be a violation of data consistency, unnecessary duplication leading to illiterate use of disk space, as well as performance problems and query building.

Your architecture allows unnecessary duplication of data, moreover, the specified architecture, as you can see, leads only to complication of the process of writing queries and complicating the process of its support.

In databases, they often do not use the method of direct deletion of a record from memory to delete data, but simply set a flag to whom this record is no longer visible and cannot be used. For example, in your case, you could create a dialog_deleted_messages table with the message_id and user_id fields that define messages that are not visible to a specific user.

When you are designing a relational database, be aware that the same data should be stored in one table, and the other tables should refer to them and only in exceptional cases store the aggregated value in order not to do extra work.

Read about database normalization - this will be useful for further database design.

Sample DB Architecture for Conversations

  1. Table to store general information on the dialogue .

    • id int
    • name VARCHAR (50)
    • created TIMESTAMP

  2. Table for storing messages of the dialogue - all messages associated with the dialogue will be stored here.

    • id int
    • dialog_id int
    • text TEXT
    • created TIMESTAMP

  3. Dialogue Table

    • dialog_id int
    • user_id int
    • joined TIMESTAMP (indicates when the participant joined the dialog, so as not to show messages that were before he joined, it is possible to replace this field with an Id offset so that it works faster)

  4. Table of hidden (deleted) messages

    • message_id int
    • user_id int