Professional Web Applications Themes

DB Design and Query Question - MySQL

Hello Everyone, I hope you enjoyed your weekend and are setting up for a great week. I sit here thinking of how to design a buddy list in SQL for a mySQL based implementation of an online community that has a concept of a buddy list. I can't seem to think of an efficient way to manage the status of buddies. Thank you in advance for any advice you may offer to the below conundrum. Basically, the buddy list should involve entities like this: USER Table : Stores a unique ID and some general profile info BUDDY Table: This is ...

  1. #1

    Default DB Design and Query Question

    Hello Everyone,

    I hope you enjoyed your weekend and are setting up for a great week.

    I sit here thinking of how to design a buddy list in SQL for a mySQL
    based implementation of an online community that has a concept of a
    buddy list. I can't seem to think of an efficient way to manage the
    status of buddies. Thank you in advance for any advice you may offer
    to the below conundrum.

    Basically, the buddy list should involve entities like this:


    USER Table : Stores a unique ID and some general profile info

    BUDDY Table: This is the table where I am having an issue. The highest
    level concept is that two unique ID become matched up. Now in
    practice, there are some states that need to be managed and I'd liek
    to solicit any recommendations from you, if possible.

    The states that need to be managed are:
    user A REQUEST to Add user B to buddy list (Pending state)
    user B DENIES adding user A (Need record of A Wanting to add B and a
    record of B denying A)
    OR
    user A REQUEST to Add user B to buddy list (Pending state)
    user B ACCEPTS adding user A (Need record of A Wanting to add B and a
    record of B Adding A)

    The ideal queries would be:
    Who is a pending Buddy?
    Who is my Buddy?

    I was thinking of two tables:
    TABLE BUDDYADD
    USERIDONE
    USERIDTWO

    TABLE BUDDYDECLINE
    USERIDONE
    USERIDTWO

    However, I can't think of an efficient way to search for Pending
    items. In this case a pending item would be where BUDDYADD USERIDTWO
    is ME AND I already didn't DENY this person. As you may guess it isn't
    a 1:1 ration, so the query would look like: Who are all the users who
    want to add me to their buddy list, so I can accept and deny them?
    Another query of interest would be: Who are all my buddies? That is,
    in TABLEADD Where USERIDONE is ME and USERIDTWO is BUDDY and USERIDONE
    is BUDDY and USERIDTWO is ME. Seems not too efficient for me. Any
    thoughts? Thank you for taking a look.

    TATrader Guest

  2. #2

    Default Re: DB Design and Query Question

    >I hope you enjoyed your weekend and are setting up for a great week. 

    I think you can do this with two tables: the User table and
    the Buddy table. The Buddy table has three fields: UserID1, UserID2,
    and Status. Status is an ENUM with these values:

    PENDING User A has requested to add User B to his buddy list.
    ACCEPTED Was PENDING, user B accepted.
    DECLINED Was PENDING, user B declined.



    Pending buddy: SELECT UserID1 WHERE UserID2 = '$me' and Status = 'PENDING';
    My buddies: SELECT UserID2 WHERE UserID1 = '$me' and Status = 'ACCEPTED';

    Gordon Guest

  3. #3

    Default Re: DB Design and Query Question

    On Jun 25, 9:17 pm, org (Gordon Burditt) wrote: 






    >
    > I think you can do this with two tables: the User table and
    > the Buddy table. The Buddy table has three fields: UserID1, UserID2,
    > and Status. Status is an ENUM with these values:
    >
    > PENDING User A has requested to add User B to his buddy list.
    > ACCEPTED Was PENDING, user B accepted.
    > DECLINED Was PENDING, user B declined.
    >
    > Pending buddy: SELECT UserID1 WHERE UserID2 = '$me' and Status = 'PENDING';
    > My buddies: SELECT UserID2 WHERE UserID1 = '$me' and Status = 'ACCEPTED';- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Gordon,

    Thank you for the response, I like it. it is in my current design
    plan, but one area I am struggling with is the buddy query might have
    to go through with a union or two queries. Example:

    My buddies: SELECT UserID2 WHERE UserID1 = '$me' and Status =
    'ACCEPTED';
    Case:
    I request USER A
    INSERT INTO BUDDIES VALUES(myId, USERA, PENDING);

    USER B requests me
    INSERT INTO BUDDIES VALUES(USERB, myId, PENDING);

    Everyone likes one another so these tables get updated with ACCEPTED.
    I'd need to union a query like:

    SELECT UserID2 WHERE UserID1 = '$me' and Status = 'ACCEPTED';
    UNION
    SELECT UserID1 WHERE UserID2 = '$me' and Status = 'ACCEPTED';

    Would this be the most efficient given cirstances of this matching
    pairs? Appreciate it very much!

    TATrader Guest

  4. Moderated Post

    Default Re: DB Design and Query Question

    Removed by Administrator
    Gordon Guest
    Moderated Post

  5. #5

    Default Re: DB Design and Query Question

    Thank you very much for the help and discussion, in fact those extra
    cases need to be accounted for. My solution was to duplicate entries
    like you recommended, with a status enum.
    USERA USERB COL1ADDCOL2
    USERB USERA COL2ADDCOL1

    TATrader Guest

Similar Threads

  1. Design query: Space Before or After?
    By Greg_Gaspard@adobeforums.com in forum Adobe Indesign Macintosh
    Replies: 13
    Last Post: October 2nd, 03:39 AM
  2. Newbie 'structure' design query
    By Kenny in forum MySQL
    Replies: 12
    Last Post: August 18th, 04:25 PM
  3. db query/design problem
    By kabbi~thkek in forum Coldfusion Database Access
    Replies: 0
    Last Post: September 30th, 11:50 AM

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
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139