Well, user_id looks like it is a unique identifier. A many-to-many relationship can only be between tables linked on fields that are not unique in either table. I think that what you are looking for is something like this:
CREATE TABLE buddylists (
listowner INT NOT NULL, /*related to user_id in users*/
buddyid INT NOT NULL, /*related to user_id in users*/
PRIMARY KEY (listowner, buddyid)
It is still a one-to-many relation. The primary key guarantees that the listowner can only add each user to their list once.
Originally posted by invictus
this seems to be many to many relation.
one user can have many buddies
the same user can be buddy of many other users
aus, good post, good solution, but one statement of yours was a wee bit weird: "A many-to-many relationship can only be between tables linked on fields that are not unique in either table." huh? not unique in either table?
also, your relationship is uni-directional
in other words, "listowner" has a "buddy", but this does not imply that "buddy" is a listowner that has listowner as a "buddy"
By "not unique in either table" I meant that a many-to-many relationship is between tables that are related on fields that can occur more than once in each table. If you were to join the buddylists table to itself by joining buddy_id to listowner to find the listowners that had each other as "buddies" that would be the many-to-many relationship that I was talking about because the buddyid and listowner can occur more than once individually in their respective columns. Is that more clear?