Many-To-Many relationship
Posted by: TA Trader
Date: June 26, 2007 12:11AM

Hello Everyone,

I was curious if anyone could lend a hand with a DB Design question I had.

Let's say I have a need to mimic an IM server, whereby a user has a buddy list or is a member of a buddy list.

so I have a table: USER with a unique ID for that user.

I am looking at an efficient way to solve the fact that someone can add me to their buddy list, but first be marked pending. I can do the same on others.

I'd like to get my buddy list with one query, or I'd like to see my pending requests with one query.

I run into trouble when I design like this:
USERID1 USERID2 STATUS

Because, let's say I request add someone:
INSERT INTO BUDDIES VALUES ($myid, $buddyid,PENDING);

and someone request add me:
INSERT INTO BUDDIES VALUES ($buddyid, $myid,PENDING);


Now let's say these both become ACCEPTED.

I want to find all my buddies with one query, hopefully without a SELECT IN and without a UNION select. Notice the issue whereby my ID might be in col1 or col2. Similar problems exist with a block feature, it's ambiguous who is blocking who.

Also, I realize since it is many to many table, I can't index the searchable columns. Anyone have a better design they may suggest?

Options: ReplyQuote


Subject
Written By
Posted
Many-To-Many relationship
June 26, 2007 12:11AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.