a simple many to many problem

invictus · #1 (**permalink**) 01-01-04, 13:32

hello guys.

I am working on a simple instant messenger software.

What would be the best way to store buddy list.

lets say i have 2 tables.

1. users(user_id,username,password)

2. profile(user_id,name,sex,nickname)

now how can i make a 3rd table to store each user's buddy.

this seems to be many to many relation.

one user can have many buddies
the same user can be buddy of many other users

Thanks in advance

Invictus

aus · #2 (**permalink**) 01-01-04, 15:59

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:

Code:

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.

Quote:

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

invictus · #3 (**permalink**) 01-01-04, 16:19

hey thanx a lot man.

that was really quick and really effective.

yeah i was wrong. it was a one to many.

Thank you.

Invictus

r937 · #4 (**permalink**) 01-01-04, 17:18

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"

if you know what i mean

rudy
http://r937.com/

invictus · #5 (**permalink**) 01-01-04, 17:33

hi again.

i'm basically a newbie in database design and stuffs.

i am working on a simple instant messenger system.

below is the link of the my database structure, i know the structure might look ridiculous coz its my first time.

could anyone (especially you r937 :P) tell me what might be the drawback of the structure. or is the database structure a total crap

?.. or whatever is my mistake. :s

the jpg for the database structure is located at

http://subrat.skcventure.com/db/my_er_diag.jpg

Thanks a ton

r937 · #6 (**permalink**) 01-01-04, 17:53

looks okay to me, invictus

but i think you'd get better feedback by posting your question again in the Database Concepts & Design forum

rudy

aus · #7 (**permalink**) 01-01-04, 19:47

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?

Internet Services

Web Development

Digital Marketing

Consumer Tech