My website allows Members to send Private Messages to each other.

On the back-end, I have things modeled this way...
Code:
member -||------0<- pm_recipient ->0-----||- private_message

member
Code:
- id
- username
and so on...

pm_recipient
Code:
- member_id_to
- pm_id
- created_on
- read_on
- deleted_on
- purged_on (This is when the Recipient empties his/her Trash)

private_message
Code:
- id
- member_id_from
- subject
- body
- sent_on
- sender_deleted_on
- sender_purged_on  (This is when the Sender empties his/her Trash)

As my code stands now, when a Member empties his/her Trash, I never physically remove any PM's. I just mark them as "purged" so they are no longer shown in a User's account.

The problem is that at some point I will need to start deleting PM's because I don't have infinite storage space?!

But how do I go about this??


Questions:

1.) If a Sender "purges" a PM, then I can't get rid of it, because the Recipients might still want to keep it.

So what do I do?


2.) Where does the storage issue really exist?

Records in the "pm_recipient" table have to be extremely small considering all they hold are "keys" and dates.

I suppose the "private_message" table takes up more space, but considering PM's cannot exceed 1024 characters, again, maybe that isn't much of an issue.

I am adding in a new feature which limits each Member to 100 PM's in their Inbox + Sent + Deleted Folders.

But does it make sense to could a PM in a person's Inbox (i.e. "pm_recipient" record) the same as a Sent PM (i.e. "private_message")?


3.) In general, what is a good strategy for cleaning out old PM's?

Things are sorta weird because I normalized my data, instead of making all PM's the same size (i.e. Sent PM = Incoming PM)

Hope this makes sense?!

Sincerely,


Debbie