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

    You'll find that the buddy list will actually contain three fields..

    The buddyListID - unique id
    The userID - the id of the user who owns the list
    many buddyID's - these are actually user Id's, but in this state,

    they will function as buddyId's.

    So this will actually be a many-many - e.g.

    tblUser

    userId

    tblUserBuddyList

    userId (list owner)
    listId
    buddyId (many user id's making up buddies)

    tblBuddyList
    listId

    Hope this helps.

      i dont think i see the point of the buddylist table?

      buddyId (many user id's making up buddies)
      and are you saying that a single field should contain a list of IDs?

      The way I now see it is like this

      tblUsers
      userID

      tblBuddyList
      UserID,
      BuddyID (actually another UserID)

      then to show a user their buddies you would need to do something like

      select BuddyList.BuddyID from BuddyList where UserID = $current_user

      what you reckon?

        Sounds fine man. Was using listId to index the buddy lists themselves to help retrieval speeds, but you shouldn't need this.

          Write a Reply...