I am creating a web community site where each user can have a list of
buddies. I have struck a bit of a problem though.
I am designing my db model and have two entities called users
containing usual stuff like password, name... and I have Buddy List.
I also have the following relation.

Users "have a" Buddy List

The cardinality is that a user can have one buddy list and one buddy
list is associated with one user.

However a buddy list contains MANY users and a single user can be in
MANY buddy lists. So do I have a many-to-many relationship? To further
confuse matters as a Buddy List is actually just a list of users is
this a recursive relationship?

Im really confused, any help welcomed!!!

well, first of all, i assume your users have some sort of unique
user_ID. All you need is a single additional table to match up
User_ID's in 2 columns. Assume you have 10 users and each has 3
buddys, the table might look like this.

tblBuddys (no primary key)
Fields: User_ID,Buddy_ID (number,indexed,dupes allowed)
1 - 2
1 - 4
1 - 6
2 - 1
2 - 8
2 - 9
3 - 1
3 - 7
3 - 10
4 - 3
4 - 5
4 - 9
.... etc.
So basically each time a user adds another user to his buddy list you
would add a new record to tblBuddys with his User_ID and his buddys
User_ID as Buddy_ID.
This way all you have to do is query this table by user_ID to get a
particular users buddy list.

SELECT Buddy_ID FROM tblBuddys WHERE User_ID = 1;
would give you user 1 buddy list.

SELECT COUNT(Buddy_ID) FROM tblBuddys WHERE User_ID = 1;
gives you the count of buddys in user 1's list

SELECT COUNT(Buddy_ID) FROM tblBuddys WHERE Buddy_ID =1;
and you can tell a user how many people have him buddied up.

SELECT User_ID FROM tblBuddys WHERE Buddy_ID = 1;
and now you can give him the list of people who have him buddied up.

g'luck

John
Applied Computer Science
jobrien AT acscience DOT com



biffta (AT) hotmail (DOT) com (David) wrote in message news:<3eb239cb.0311091655.364a1d54 (AT) posting (DOT) google.com>...