Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2004
    Posts
    8

    Unanswered: a simple many to many problem

    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

  2. #2
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137

    Re: a simple many to many problem

    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.

    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

  3. #3
    Join Date
    Jan 2004
    Posts
    8
    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

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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/

  5. #5
    Join Date
    Jan 2004
    Posts
    8

    Unhappy

    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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

  7. #7
    Join Date
    Oct 2003
    Location
    Denver, Colorado
    Posts
    137
    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •