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.

>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 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';

On Jun 25, 9:17 pm, gordonb.qo...@burditt.org (Gordon Burditt) wrote:
> >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 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 -

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 circumstances of this matching
pairs? Appreciate it very much!

>> >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 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 -
>
>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:

In my view, "A is a buddy of B" and "B is a buddy of A" are completely
separate. Thinking of it another way, "A is a stalker of B" doesn't
mean "B is a stalker of A", although I suppose you can have two
people stalk each other. Worse, "A is an uncle of B" doesn't mean
"B is an uncle of A", although if you watch the Jerry Springer show
long enough, you'll find an example of mutual uncleship.

There's two buddy lists, those *I* invited and those who invited
*me*. Say, if *I* invited *them*, they can look at *my* private
pages for buddies only. If *they* invited *me*, I can look at
*their* private pages for buddies only. Those lists might be mostly
identical for most people. Now, it's quite possible that my mother
insists that I invite her as a buddy so she can look at my pages,
otherwise she won't let me sign up, but she doesn't want me looking
at nude pictures (or lack thereof) of *her* on her pages. That's
a reason for non-mutual buddyship.

>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 circumstances of this matching
>pairs? Appreciate it very much!

You might manage this based on what happens when one buddy relationship
is proposed and the other accepts. If buddyship must be mutual,
then you could create "ACCEPTED" records that go both ways (one
already exists as pending). That eliminates the union, but costs
twice as much in table entries and some queries in establishing
buddyship. It also means that things *could* get screwed up and
you accidentally have one-way buddy relationships that stick around.
I recommend the use of transactions so changing one status to
ACCEPTED and inserting the record going the other way either both
happen or both don't.

I presume generating buddy lists is frequent and establishing
buddyship with another person is much less common.

Other things to think about:

If one person proposes buddyship and the other person declines, can
the proposal be done again? How soon? Do you then get rid of the
DECLINED record once the proposing person is notified of the decline,
or after a specific time (in which case you need a timestamp in the
record). If one person proposes buddyship and the other person
accepts, can either person change his mind later?

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