SitePoint Sponsor

User Tag List

Results 1 to 9 of 9
  1. #1
    SitePoint Evangelist barbara1712's Avatar
    Join Date
    Apr 2007
    Location
    India
    Posts
    509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to design database

    Hello All,

    I want to bulid internal email portal system.
    In this if user1 sends the message to user2,then @user1 the message will be displayed in 'sent' folder and @user2 the message will be displayed in the 'inbox' folder.right?
    Now if the 'user1' wants to delete that message and if he deletes the message then it will be deleted (or move to 'trash' folder and he can also delete the message from trash folder).But the user2 still can see the message to his 'inbox' folder to reply or forword it.Here I don't know how do I manage if user1 deletes the message it should not get deleted for user2


    Could you please help me how do I design the database?

    Your help will be highly appriciated.

    Thanks,
    Barbara

  2. #2
    SitePoint Evangelist
    Join Date
    May 2006
    Location
    Denmark
    Posts
    407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    There are a couple of ways.

    You could have to fields in the table called sender_deleted and recipient_deleted.

    You could also store the message in one table and then you could store ownership in another table. That means that when you send a message then you would insert 1 row into the messages table and 2 rows into the messages_ownership table (one for the sender and one for the recipient). When someone deletes the message just remove the corresponding row in the ownership table.

  3. #3
    SitePoint Evangelist barbara1712's Avatar
    Join Date
    Apr 2007
    Location
    India
    Posts
    509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks,
    you mean in message_ownership table there will be 2 fields i.e.
    messageid and userid. For each inserting message there will be 2 rows like
    messageid userid
    1 5
    1 8

    is it so?
    and then what about messages deleted from trash?
    Barbara

  4. #4
    SitePoint Evangelist
    Join Date
    May 2006
    Location
    Denmark
    Posts
    407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    For the trash you could just have another field called folder.

  5. #5
    SitePoint Evangelist barbara1712's Avatar
    Join Date
    Apr 2007
    Location
    India
    Posts
    509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    In which table I should place field called folder?
    Barbara

  6. #6
    SitePoint Evangelist
    Join Date
    May 2006
    Location
    Denmark
    Posts
    407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Seeing as it's related to the specific ownership it should be in that table.

  7. #7
    SitePoint Evangelist barbara1712's Avatar
    Join Date
    Apr 2007
    Location
    India
    Posts
    509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    means
    I will have folder_master table in which I will store the data like
    fid folder
    1 inbox
    2 sent
    3 trash

    When user1(id say 5) sends the message to user2(id say 8) then, 1 record for message will be inserted into message table. And 2 records will get inserted into ownership table like

    msgid userid fid
    1 5 2
    1 8 1

    And when user2(id=8) deletes the messgae from inbox, instead of deleting the record from ownership, I will update the record and set the fid=3
    same when user1(id=5) deletes the record from sent,update the fid = 3

    Now for Trash
    When trash message is deleted then just delete the corresponding row from ownership table.

    This is what I understand from above discussion.
    Please tell me wethere I am on right track or not?

    Thanks,
    Barbara

  8. #8
    SitePoint Evangelist
    Join Date
    May 2006
    Location
    Denmark
    Posts
    407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Sounds fine to me

  9. #9
    SitePoint Evangelist barbara1712's Avatar
    Join Date
    Apr 2007
    Location
    India
    Posts
    509
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok I will give a try and will let you know.

    Thanks a lot for your suggestions....
    Barbara


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
  •