SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Zealot
    Join Date
    Sep 2008
    Location
    HidePoint
    Posts
    195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Database-Designing for status messages

    I am building a project in which i make use of status messages

    There are two entity groups - Restaurants and Users.

    Both can have status messages and both can comment on each other. I am in a fix as to how to get it working with these two entity blocks..

    Here goes my try

    restaurants (id, name, ...... )
    users (id, name, .... )
    status_messsage (id, message, timestamp )
    status_owner (id, status_message_id, by_type, by_id)
    by_type ( id, group_name ) => (1, users), (2, restaurants)

    by_id => will be the user id or restaurant_id


    i feel this is a little messed up. is there a better solution.

  2. #2
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I would lose the by_type and have

    status_owner (id, status_message_id, restaurant_id, user_id)

    with restaurant_id being null or zero when user_id is non zero
    and user_id being null or zero when restaurant_id is non zero

  3. #3
    SitePoint Zealot
    Join Date
    Sep 2008
    Location
    HidePoint
    Posts
    195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PhilipToop View Post
    I would lose the by_type and have

    status_owner (id, status_message_id, restaurant_id, user_id)

    with restaurant_id being null or zero when user_id is non zero
    and user_id being null or zero when restaurant_id is non zero
    then i can do this right ?
    restaurants (id, name, ...... )
    users (id, name, .... )
    status_messsage (id, message, timestamp, restaurant_id, user_id )

  4. #4
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes. But ...

    You talked about both groups, restaurants and users, being able to do status messages and comments. Status messages implies a finite set of possible messages whereas comments suggest a different piece of text each time. For comments this new arrangement would be appropriate but for status messages (when there is a finite set) I would put them in a separate table and use the statusmessage_id, as in you original arrangement.

  5. #5
    SitePoint Zealot
    Join Date
    Sep 2008
    Location
    HidePoint
    Posts
    195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PhilipToop View Post
    Yes. But ...

    You talked about both groups, restaurants and users, being able to do status messages and comments. Status messages implies a finite set of possible messages whereas comments suggest a different piece of text each time. For comments this new arrangement would be appropriate but for status messages (when there is a finite set) I would put them in a separate table and use the statusmessage_id, as in you original arrangement.
    didnt get you on the finite part. can u give an example plz.

    thanks

  6. #6
    SitePoint Evangelist
    Join Date
    Jun 2007
    Location
    North Yorkshire, UK
    Posts
    483
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    finite = limited number, a distinct list of possible values e.g. (Created, Opened, closed, Deleted)

  7. #7
    SitePoint Zealot
    Join Date
    Sep 2008
    Location
    HidePoint
    Posts
    195
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by PhilipToop View Post
    finite = limited number, a distinct list of possible values e.g. (Created, Opened, closed, Deleted)
    oops.. guess u have mis understood the field.. status message is some think like a tweet. or status messge of facebook.


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
  •