How to get rid of PM's?

Databases
1

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

On the back-end, I have things modeled this way…


member -||------0<- pm_recipient ->0-----||- private_message

member


- id
- username
and so on...

pm_recipient


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

private_message


- 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

2
  1. You could only purg if the recipient deletes the PM
  2. Unless you are having a facebook/twitter size userbase then the PM storage really isnt going to be an issue.
  3. Keep everything, trust no one.
3

How do I regulate that?

  • Trigger?

  • Application Code?

  • Cron Job?

You sure about that??

Please explain more what you mean…

Debbie

4

Any of the three would be applicable, but as I intimated before - deleting is optional!
If pushed I would probably set up the application code to remove the PM there and then.

You could always move it to an archive table which periodically gets exported and then flushed but that’s making work for yourself!

From the MySQL Docs: http://dev.mysql.com/doc/refman/5.6/en/features.html
[h=4]Scalability and Limits:[/h][COLOR=#000000][FONT=Helvetica]

  • Support for large databases. We use MySQL Server with databases that contain 50 million records. We also know of users who use MySQL Server with 200,000 tables and about 5,000,000,000 rows.

[/FONT][/COLOR]
So realistically yes, you should not have storage issues .

Keep all evidence of conversations and papertrails intact as you never know when they may be required… just sayin…

5

Okay.

Okay.

You’re missing my concern…

MySQL might store the entire world, but GoDaddy will charge me an arm and a leg if I start storing Tera-Byes of old PMs?! :eek:

Yes, that makes sense.

And with that being said - combined with my fear of extra hosting costs - how much space do you PM’s will take?

Any guestimates - or scientific ways to calculate - how many of my websites PM’s will equate to, say 1 MB?

Right now things are Text-only, and PM’s cannot exceed 1024 characters, and most of the fields in the tables in my OP are either “key” fields" or “timestamp” fields, so each record probably doesn’t take up that much room.

Then again, beyond my storage concerns, allowing people to store all PM’s from the beginning of time creates other work for me. For example, it would force me to have to add Pagination.

Anyone else have any thoughts on this?

Sincerely,

Debbie