PHP Private Message (PM) System

Question

I am planning making a PM system for my users, overall it seems easy enough, but the way I have seen tutorials making PM systems, there is one problem.

In the way i planned it to work, there would be rows like, user_from, user_to and then the message - user_from would be the sender, and will see the message in his send messages, user_to will be the receiver and will see the message in his inbox. BUT, what if a user wants to delete a message from their sent folder, but the other user does not want to delete it from their inbox ??

Is there any simple way doing this ?

It could also be nice, to have the messages in conversations, like Gmail and Facebook, but that is maybe to hard to code (any tutorials appreciated)?

Scranton

Answer 1

You can fix the issue a few ways, but I would probably add a couple flags (from_deleted, to_deleted) to the table:

• Instead of deleting the message, update the appropriate flag to 1.
• When listing messages, filter out those that have been flagged.
• You can setup the script so that after flagging, if both fields are flagged then you can actually delete the row.

Indecorous

Sounds like the other suggestions, deleting the row when both have flagged the message as deleted was not mentioned though, thank you. :-) – Scranton

Answer 2

Use what's called a soft delete. This means when a record is 'deleted' it is never actually removed from the database but rather a flag is set to delete which allows you to remove it from a user interface while still having access to the data when you need it. So for this situation you could create two more columns called user_to_delete and user_from_delete. When either of these is set to true you would know not to show the message in the respective user's inbox/outbox. Goodluck.

Hegel

Answer 3

I suggest the following database design:

MESSAGES
+----------+------------------+---------------------------+
|    id    |    subject_id    |    body                   |
+----------+------------------+---------------------------+

SUBJECTS
+----------+-------------+--------------+-----------------+
|    id    |    title    |    author    |    receivers    |
+----------+-------------+--------------+-----------------+

INBOX
+----------+---------------+--------------+---------------+
|    id    |    user_id    |    msg_id    |    read       |
+----------+---------------+--------------+---------------+

OUTBOX
+----------+---------------+------------------------------+
|    id    |    user_id    |    subject_id                |
+----------+---------------+------------------------------+

When you send messages, you create a new row for all receivers in the inbox table, and in the outbox table one for the sender. In the messages table you insert one row with the ID of the subject and the message body. In the subjects table you insert one row with the title, author and all receivers (if the sender started a new subject or forwarded a full conversation or single message, otherwise add the message in the messages table using the existing subject ID) so that this info is kept even if one of the receivers deletes a messages from his/her inbox (in this case delete the row in the inbox table).

For the outbox there is no need for a 'read' flag, and notice that only the subject ID is used.

Messiaen

Answer 4

Yes, there is a simple way of doing it! Having two more columns, respectively sender_deleted and receiver_deleted. If one of them "deletes" the message, then you updated the column with a value of 1. for example. When you display the messages, you select the messages you make sure the value is different than 1. Etc...

Saddleback

Answer 5

Another approach would be add two columns that will determine whether or not the owner or recipient have requested to delete (hide) the message.

owner_id | user_from | user_to | mailbox_folder | Message | Owner_hide | Recipient_hide
1          1           2         Sent             Hi         1           0

Depredation

Answer 6

You would just create 2 rows, and add a column. example:

owner_id | user_from | user_to | mailbox_folder | Message
1          1           2         Sent             Hi
2          1           2         Inbox            Hi

Other columns: a unique row id, timestamps, subject line, etc...

Your mailboxes would then be built off of the owner_id column, and each user has their own copy to move/delete as they choose.

To add conversations, you could add a column, or another table. If it's a new message, get a new conversation id, otherwise use the same ID. Query by timestamps.

Taken

this adds a lot of duplicate data. Database Normalization – Herrah

@Herrah /shrug.. it emulates every other system. – Taken

That's very space inefficient. No matter how many people use it, it's still bad design. Copying bad design is even worse than creating it. – Lenni

Glad you two could pat each other on the back there. – Taken

Answer 7

You could add a user_from_deleted & user_to_deleted rows and display a message only if it is not deleted.

To display the messages in conversations you could add an parent_id and display all the messages with the same parent_id

Streeto

Answer 8

A PM system is a little more complex that one table. What if you PM more than one person? In this case you would want multiple tables. One for users, messages, etc... You would link them up using primary and foreign keys.

Try looking up relational databases. This should get you started: http://www.databasejournal.com/sqletc/article.php/1469521/Introduction-to-Relational-Databases.htm

Herrah

That sounds very comlpex, might look at it, but i don't think my skills is that good yet :-) – Scranton

It will save you time later on down the road, when you might want to add more features. Just have a few tables: users, messages, and a table that links the ids of users and messages to each other for the users and messages(i call them linking tables, but that might not be the correct wording :/). – Herrah