Surveillance of personal messages and conversations on mysql



  • I have two data storage options:


    1. Keep each dialogue in a separate table so that everyone can read the information from the table. It solves the problem when one interviewer removes the message and the other wants to keep it. And when a new interviewer was added, his story would be empty.

    2. Keep everything in the same table and use the parties. Pros and cons of two approaches? Your option?

    3. What if one of the users removed the whole conversation?



  • First, read about the normalization of the database, if you do it on the relay database, Forget the creation of separate tables. to store the same data. Single data should be stored in one table.

    The problem is that you have one interviewer removed the message message, and the other is determined that the records are not necessarily removed from the OBD system, but that it is sufficient to pass the flag that the user removed the message. And about the table or the ballding on the servers, you're gonna need it on millions of records, so not soon.

    The structure, subject to the establishment of this in the relay structure, you should have some next, tried to describe quickly, so I'm sorry if you didn't take anything into your architecture:

    1. Table for general information on dialogue

      • id INT
      • name VARCHAR(50)
      • created TIMESTAMP
    2. Table for the storage of dialogue messages - All communications linked to dialogue will be stored here.

      • id INT
      • dialog_id INT
      • text TEXT
      • created TIMESTAMP
    3. Table of participants

      • dialog_id INT
      • user_id INT
      • joined TIMESTAMP (indicates when the participant joined the dialogue in order not to show the messages that were received before he joined, perhaps to replace the field with the substitution of Id to work faster)
    4. Table of hidden (remote) messages

      • message_id INT
      • user_id INT



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2