MySQL: user trigger to fill values of a table?

[dancks]

I am creating a basic PM system for my website. I have just a basic table for a pm:

mysql>describe PM;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| PMID         | mediumint(9) | NO   | PRI | NULL    | auto_increment | 
| toID         | mediumint(9) | NO   |     |         |                | 
| fromID       | mediumint(9) | NO   |     |         |                | 
| to_name      | varchar(100) | YES  |     | NULL    |                | 
| from_name    | varchar(100) | YES  |     | NULL    |                | 
| about_itemID | mediumint(9) | YES  |     | NULL    |                | 
| about_item   | varchar(100) | YES  |     | NULL    |                | 
| title        | varchar(100) | NO   |     |         |                | 
| body         | text         | NO   |     |         |                | 
+--------------+--------------+------+-----+---------+----------------+

and this table:

mysql> describe User_has_PMs;
+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| PMID      | mediumint(9) | NO   |     |         |                | 
| isvisible | tinyint(4)   | NO   |     | 1       |                | 
| userID    | mediumint(9) | NO   |     |         |                | 
| UHPID     | mediumint(9) | NO   | PRI | NULL    | auto_increment | 
+-----------+--------------+------+-----+---------+----------------+

The reason I have a User_has_PMs table is because I want users to have the ability to remove PMs, but obviously since PMs will show up in more than one mailbox I can't let them just delete PMs, so I firgured I would have a table that stores which PMs are visible to a user and which aren't.

I was hoping I could make my life simple and use a CREATE TRIGGER to auto_fill this table when a user sends a PM. But I'm curious if this would work as this would be a back-to-back query on the same data, a write followed by a read and I'm afraid even if it lets me do this, that it might time out. I could do this with PHP, and I'm not opposed to that but I was hoping for something easy like a trigger.

What are your thoughts?

[AleMonteiro]

If I understood you structure, if a user send the same PM to multiple users, this PM will be inserted multiple times into the PM table. Is that correctly? I assumed this because of the ToId field.

If I supposed correctly, then you don't need the second table. Because when a user deletes the recieved PM you'll only update or delete it's own record and not from every user that recieved that PM.

That said...

Another way of doing this, that I prefer, is to avoid duplicating the PM's. So you'd have an PM table only with FromID and the PM settings, and another table PM_Receivers (in example) that would relate the PM with the receivings users.
This way the body of the PM (that can be large sometimes) will be stored only once, and you have flexibility to handle users options to delete, hide, restore and etc the receiveds PM's.

This way a trigger woudn't be helpfull, because the PM table would not have the ToID to be inserted on the other table, you'd have to do it with PHP.

Hope it helps.

[dancks]

No. A PM is only inserted once, and is read by both parties. A PM can only have 1 recipient.

I'm looking into doing with just PHP. The table is for when the site gets going, and there might be a lot of PMs sent, a user should have the ability to 'delete' said PMs, but if its reading the same data as the other guy, I can't simply delete that PM, and hence the second table which tells me who is the PM visible to.

the mailbox is simply 2 tables: Sent and Received.

I was gonna do a mysql statement like:

select toID,fromID,title from PM join User_has_PMs on PM.toID=User_has_PMs.userID AND User_has_PMs.isvisible=1;

so they only see the PMs they want to see.

(FYI, I'm an SQL noob, and for some reason that statement above is duplicating an entry for some reason.) I don't understand why.

here's my sample data:

mysql> select PMID,toID,fromID,title from PM;
+------+------+--------+------------------------------+
| PMID | toID | fromID | title                        |
+------+------+--------+------------------------------+
|    1 |    1 |      2 | You really want this product | 
|    3 |    2 |     31 | I want that anvil            | 
+------+------+--------+------------------------------+
2 rows in set (0.00 sec)

mysql> select * from User_has_PMs;
+------+-----------+--------+-------+
| PMID | isvisible | userID | UHPID |
+------+-----------+--------+-------+
|    1 |         1 |      1 |     1 | 
|    1 |         1 |      2 |     2 | 
|    3 |         1 |      2 |     3 | 
|    3 |         1 |     31 |     4 | 
+------+-----------+--------+-------+
4 rows in set (0.01 sec)

[AleMonteiro]

Oh Ok. So if a PM can be sent just to one user, there only two options of deleting the message: either the user who sent deleted it or the user who receieve deleted it, or both. So you could just add two columns on the PM Table: ToVisible, FromVisible.

It's simpler, isn't it?

[dancks]

Yes. that is simpler. Thanks.