#1
  1. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,082
    Rep Power
    379

    simple message/email/inbox system?


    I am struggling to come up with a schema for message system (simplified) so user 1 sends message to user 2, user 2 replies back.

    Here is what I have come up with so far
    Code:
    +--------------+---------------------+------+-----+---------------------+----------------+
    | Field        | Type                | Null | Key | Default             | Extra          |
    +--------------+---------------------+------+-----+---------------------+----------------+
    | id           | bigint(20) unsigned | NO   | PRI | NULL                | auto_increment |
    | user_id      | bigint(20) unsigned | NO   | MUL | NULL                |                |
    | recepient_id | bigint(20) unsigned | NO   | MUL | NULL                |                |
    | subject      | varchar(30)         | NO   |     | NULL                |                |
    | message      | text                | NO   |     | NULL                |                |
    | is_read      | tinyint(1)          | NO   |     | 0                   |                |
    | parent_id    | bigint(20) unsigned | NO   |     | 0                   |                |
    | created_at   | timestamp           | NO   |     | 0000-00-00 00:00:00 |                |
    | updated_at   | timestamp           | NO   |     | 0000-00-00 00:00:00 |                |
    +--------------+---------------------+------+-----+---------------------+----------------+
    and some sample data:
    Code:
    +----+---------+--------------+---------+-----------+
    | id | user_id | recepient_id | is_read | parent_id |
    +----+---------+--------------+---------+-----------+
    |  1 |      19 |           61 |       1 |         0 |
    |  2 |      61 |           19 |       1 |         1 |
    |  3 |      19 |           61 |       1 |         1 |
    |  4 |      61 |           19 |       1 |         1 |
    |  5 |      19 |           61 |       0 |         1 |
    |  6 |       9 |           61 |       1 |         0 |
    |  7 |      61 |            9 |       0 |         6 |
    |  8 |      61 |            1 |       1 |         0 |
    |  9 |       1 |           61 |       0 |         8 |
    +----+---------+--------------+---------+-----------+
    The problem I am having is how do I list all messages in user_id 61's inbox.. and highlight some messages if they have not been read

    An example would show:

    Messages with ID 1, 6, 8

    Message with ID 1 & 8 - highlighted because messages with id 5 & 9 are unread.

    Not sure if i should rethink the schema
  2. #2
  3. Lazy Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    16,436
    Rep Power
    9645
    The schema seems fine with me, but... 1, 6, and 8? If you're talking about the inbox then shouldn't you be looking at the rows with recipient_id (you misspelled that) = 61? That's 1, 3, 5, 6, and 9, with only 9 being unread.

    Personally, I would name user_id as sender_id (that way you have "sender" and "recipient" in the table), then parent_id more like conversation_starter_id or something like that so it's obvious that the column indicates the first message in the "conversation", where parent sounds more like a previous message (like the message being replied to).
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Dec 2004
    Posts
    3,082
    Rep Power
    379
    thanks for the reply You are right but with that sample data i was showing that messages
    1. with id of 2-5 are sub-child of id =1 so on inbox page they wouldnt show up but rather id=1 would show up but be highlighted because sub-child id=5 is unread
    2. with id of 6-7 are related but will not show up as highlighted because in id=7 I sent the message to user=9 so for them it would be highlighted

    Ok this also leads me with TWO choices of showing the inbox?
    1. either show the original message highlighted if there is a reply - I have managed to do this now..
    2. or show the latest message...

    which would be more acceptable or User Friendly?

    Also one more question about deletion? how do i add that in? if i was to delete the parent (not actually delete but mark it as adelete in db)
    I was thinking to have two more columns sender_deleted and recepient_deleted? and check those if yes dont show?

    but say I can i either delete the whole conversation like the parent_element or individual messages.. again which one is more UI/UX ?

    & thanks for the rename suggestions.. make sense..

IMN logo majestic logo threadwatch logo seochat tools logo