SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Zealot TheMonk's Avatar
    Join Date
    Oct 2005
    Location
    Canada
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question about Private Messaging System

    Hi,

    I'm currently building a private messaging system for users to send a pm to another user, like you see on many other sites. Most of it so far is pretty straight forward.

    However I'm trying to get my head around a certain issue. I have my own solution but wanted to poll the community to see if there is a better way to do this.

    I'm still establishing my tables but they will look something like so:

    Message
    -------
    id
    message = text
    date = date
    read = boolean

    Message Join
    -------------
    sender = id (existing profile id)
    recipient = id (existing profile id)
    message = id (from message table)


    There is already an existing user table that I'm tapping into. So here's my dilema. I want to give each user the ability to delete the message from their message inbox / outbox. But if only one person deletes the message, the message record itself should remain in the message table until both people delete it. (hope that's not too confusing)

    So my original though was that every time a request to delete the message comes in, the site should check to see if anyone else is linked to the message. (has the other person deleted it?) If there are no other links, delete the message. Otherwise if someone is still linked to the message remove the link between the current user and the message but leave the message record in the DB.

    Any thoughts or opinions on this setup? Is this the best method if I want to keep the message until it's no longer in anyone's inbox?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    you might want to keep the message anyway

    wouldn't that simplify a lot of code? (hint: yes)

    can a message have two senders? probably not, so save the sender id in the messages table

    then your second table has message id plus recipient id, and the "read" column should be here as well

    in addition, you could have a "location" or "folder" column which you update with the location where the recipient wants it filed -- this lets users put a message in whatever folder they want, so a message starts out in in the recipients' inboxes but each recipient can "move" the message into a folder, and you could use Delete as the name of the folder for deleted messages
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot TheMonk's Avatar
    Join Date
    Oct 2005
    Location
    Canada
    Posts
    143
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The reason I want to delete the message after time is to keep my databases tidy and reduce bloat over time.

    Good points about the data normalization, I'll make sure to edit the structure accordingly.


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
  •