Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2009

    Unanswered: Schema Desig for Private Message System

    Hi guys,

    I am developing a website where I have a requirement of "Private Messaging System". Registered user will have an ability to compose messages to any registered users. There is no limit to the number of messages one user can send to another. This looks like a case of "Many To Many" relationship.

    My requirement is as follows :

    When logged in user in my website clicks on 'Messages Sent', it should list all the messages which logged in user has sent to different members.

    When logged in user clicks on 'Inbox', it should list all the messages which other members have sent to the currently logged in user.

    It is more like an email system design (where we have concepts like Inbox, compose, sent items etc etc).

    Currently the table design which I have is as follows :

    Table : Messages

    id int(10) NOT NULL auto_increment,
    message_from int(10) default NULL,
    message_to int(10) default NULL,
    subject varchar(66) default NULL,
    message varchar(250) default NULL,
    message_status varchar(3) default NULL,
    replied varchar(3) default NULL,
    sent_date bigint(26) default NULL,
    read_date timestamp NULL default NULL,
    replied_date timestamp NULL default NULL,
    PRIMARY KEY (id)

    But this table is not able to cater my requirement.

    Appreciate if anyone can shed some lights on the DB schema design to cater this requirement.


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    a message can be from only one user, and this is accomplished by the FK message_from

    however, a message can typically be to multiple users, and thus you would not want the FK message_to in this same table

    similarly, when a message is read by one of the users it was sent to, the read_date column that is updated must relate to the recipient, not just the message itself

    so you need another table

    make sense? | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2003
    Oops.. I need to redesign a private message table that I've using. It works for a strictly one-sender to one-recipient scenario, but won't if it's one-to-many.

    Thanks r937!

Posting Permissions

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