Implementing a BUDDY list

[David]

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

[D. Pickering]

you need 2 tables. A user table (as you have described) and a buddy table
with basically 2 columns. UserID, BuddyID.
So if user 2 has buddies 4,5,8, 17 then there would be 4 rows in the buddy
table
2,4
2,5
2,8
2,17
to list 2's buddies
select * from buddy where UserID=2. (gives you id's of 2's buddies)
or select * from buddy left join users using (UserID)
or select user.name as Uname from buddy left join users using (UserID) and
the Uname(s) are 2's buddies.
dp

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

[D. Pickering]

you need 2 tables. A user table (as you have described) and a buddy table
with basically 2 columns. UserID, BuddyID.
So if user 2 has buddies 4,5,8, 17 then there would be 4 rows in the buddy
table
2,4
2,5
2,8
2,17
to list 2's buddies
select * from buddy where UserID=2. (gives you id's of 2's buddies)
or select * from buddy left join users using (UserID)
or select user.name as Uname from buddy left join users using (UserID) and
the Uname(s) are 2's buddies.
dp

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

[David]

thanks a lot!

[David]

thanks a lot!