SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Hybrid View

  1. #1
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,528
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    How to get rid of PM's?

    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

  2. #2
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,788
    Mentioned
    151 Post(s)
    Tagged
    3 Thread(s)
    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.
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  3. #3
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,528
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by spikeZ View Post
    1) You could only purg if the recipient deletes the PM
    How do I regulate that?

    - Trigger?

    - Application Code?

    - Cron Job?


    Quote Originally Posted by spikeZ View Post
    2) Unless you are having a facebook/twitter size userbase then the PM storage really isnt going to be an issue.
    You sure about that??


    Quote Originally Posted by spikeZ View Post
    3) Keep everything, trust no one.
    Please explain more what you mean...


    Debbie

  4. #4
    dooby dooby doo silver trophybronze trophy
    spikeZ's Avatar
    Join Date
    Aug 2004
    Location
    Manchester UK
    Posts
    13,788
    Mentioned
    151 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by DoubleDee View Post
    How do I regulate that?

    - Trigger?

    - Application Code?

    - Cron Job?
    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!

    Quote Originally Posted by DD

    You sure about that??
    From the MySQL Docs: http://dev.mysql.com/doc/refman/5.6/en/features.html
    Scalability and Limits:


    • 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.



    So realistically yes, you should not have storage issues .

    Quote Originally Posted by DD
    Please explain more what you mean...
    Keep all evidence of conversations and papertrails intact as you never know when they may be required.... just sayin.....
    Mike Swiffin - Community Team Advisor
    Only a woman can read between the lines of a one word answer.....

  5. #5
    SitePoint Wizard DoubleDee's Avatar
    Join Date
    Aug 2010
    Location
    Arizona
    Posts
    3,528
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by spikeZ View Post
    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.
    Okay.


    Quote Originally Posted by spikeZ View Post
    You could always move it to an archive table which periodically gets exported and then flushed but that's making work for yourself!
    Okay.


    Quote Originally Posted by spikeZ View Post
    From the MySQL Docs: http://dev.mysql.com/doc/refman/5.6/en/features.html
    Scalability and Limits:


    • 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.



    So realistically yes, you should not have storage issues .
    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?!


    Quote Originally Posted by spikeZ View Post
    Keep all evidence of conversations and papertrails intact as you never know when they may be required.... just sayin.....
    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


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •