FRIHOST • FORUMS • FAQ • TOS • BLOGS • DIRECTORY
You are invited to Log in or Register a Frihost Account!

Multiple Many-to-Many relationship

   Frihost Forum Index -> Scripting -> Php and MySQL
 


kawkazEE
im trying to build an online community that each registered member can add other members to their lists of friends. i found something while googling that i should use a separate table that will served as the connection but what if i have hundreds of members should i create a separate table for them? if any body knows the solution please help.

Sad
SlowWalkere
What you're looking to make is a list of friends for each user. The way I see it, there are only two logical ways to do it.

The way that you're asking about would be best. You make a table called friendslist or something and it contains two fields - the user id of the owner of the friends list (owner) and the user id of the friend (friend).

Then, when you want to get the list from the database, you do a simple select query like so...
Code:
SELECT * FROM friendslist WHERE owner = '$ownerid'

If you want more information about the friend to be displayed, use a table join to add all of the information from the users table to the select query.
Code:
SELECT * FROM friendslist INNER JOIN users ON friend = userid WHERE owner = '$ownerid'


The only other way I could think to do it would be to create a table with two fields - owner's id, and a long string that would contain a delimited list of his friends ids. In that list you'd separate each id by some kind of delimiter, such as a semi-colon, so when you get the field back to php you can explode it into an array. Although this might save you a little bit of space (since you don't have to duplicate the owner's id in every record) I think its less efficient and would cause trouble down the road (i.e. when you're deleting friends), so I wouldn't recommend it.

So, yes, my vote would be to create a separate table for the friends list.

Good luck,
- Walkere[/code]
muggle
I see you already got an answer, so I’ll probably repeat part of it in a slightly different manner, sorry for that.

You don’t need to create a separate table for each member. One table is enough for all of them. You can have one table let’s say MEMBER_LIST, which will have at least the following columns MEMBER_NAME and MEMBER_ID; than you can have another one called FRIENDS_LIST which will have the following columns MEMBER_ID and FRIEND_ID which both will contain ID from the MEMBER_LIST table. MEMBER_ID will identify uniquely each member. In this case for each member you will have a set of records each containing his own ID and the ID of one of his friends. Let’s say you have MEMBER_LIST table containing the following data:
    MEMBER_NAME, MEMBER_ID
    John, 101
    Paul, 102
    Victor, 103
    Marry, 104
    Emma, 105


If John has two friends Paul and Emma and Paul has two friends Marry and Emma and Marry has one friend Emma, your FRIENDS_LIST table will look like follows:
    MEMBER_ID, FRIEND_ID
    101, 102
    101, 105
    102, 104
    102, 105
    104, 105


It's an interesting question if you’ll consider the relation `friendship` as symmetric e.g. if John has Paul for friend => Paul also has John for friend; if so you have to add extra records to cover this situation, with the extra added records the table FRIENDS_LIST will look like follows:
    MEMBER_ID, FRIEND_ID
    101, 102
    101, 105
    102, 101
    102, 104
    102, 105
    104, 102
    104, 105
    105, 101
    105, 102
    105, 104
kawkazEE
muggle, can u give an example on how search a friend?
yy1124
kawkazEE wrote:
muggle, can u give an example on how search a friend?


As explained by Muggle, we use inter-table when normalizing m2m relationships.

Taking Muggle's example,

Table: MEMBER_LIST
MEMBER_NAME, MEMBER_ID
John, 101
Paul, 102
Victor, 103
Marry, 104
Emma, 105

Table: FRIEND_LIST
Note: FRIEND_ID is a foreign key, which links to MEMBER_ID in MEMBER_LIST
MEMBER_ID, FRIEND_ID
101, 102
101, 105
102, 104
102, 105
104, 105

When I wanna find friends of John, whose member ID is 101, i can query:
SELECT * FROM MEMBER_LIST
WHERE MEMBER_ID IN (SELECT FRIEND_ID FROM FRIEND_LIST WHERE MEMBER_ID = 101)

The sub-query will retrieve a list of FRIEND_ID which associate with John, and the main query will give you the details of these members.

Another thing which I like to point out, by using inter-table, you can have to kind of friend relationships. The last time I did the similiar project I called them "2-way relationship" and "1-way relationship".

You may notice in the table design, "John is the friend of Emma" in the table, may be
MEMBER_ID, FRIEND_ID
101, 105
or
MEMBER_ID, FRIEND_ID
105, 101

So which one should you load to table for this relationship?

I will not talk about other design first. In my design, I only load one of them. For example, when John add Emma, the record will be
MEMBER_ID, FRIEND_ID
105, 101
Now in my design, emma is the friend of John, Emma can see the details of John, but John can't see Emma's details. Why? because Emma did not add John's as friend. This is what I called "1-way relationship".

For "2-way relationship", where both can see details of each other, the table will include
MEMBER_ID, FRIEND_ID
105, 101
101, 105

This is useful when John deleted Emma as his friend but Emma don't wants to.


Again, this depends on how you want it to be designed.

You can load both record, delete both when either one "break" their relationship; or you can load only one of them, but when you query you may want to check both MEMBER_ID, FRIEND_ID (IF you use this way I would recomend you to change the column name). Exampple query for this will be:
SELECT * FROM MEMBER_LIST
WHERE MEMBER_ID IN (SELECT FRIEND_ID FROM FRIEND_LIST WHERE MEMBER_ID = 101) OR MEMBER_ID IN (SELECT MEMBER_ID FROM FRIEND_LIST WHERE FRIEND_ID = 101)

(Assuming FRIEND_ID will be changed to another name)
muggle
I think you've got a good answer from yy1124. Please tell us if you need more details.
kawkazEE
whoah! yoy guys are so great! it gets clearer everytime i post. but let me ask you this, whats should i choose "1 way relationship" or the other one? cause in real life you cant consider B(you as being A) as your friend if B dont consider you as a friend, but do you think the "1 way" can give a great feeling or some kind of convenience?

i need your opinion cause in the end, i dont want see my members moving away... Crying or Very sad
yy1124
kawkazEE wrote:
whoah! yoy guys are so great! it gets clearer everytime i post. but let me ask you this, whats should i choose "1 way relationship" or the other one? cause in real life you cant consider B(you as being A) as your friend if B dont consider you as a friend, but do you think the "1 way" can give a great feeling or some kind of convenience?

i need your opinion cause in the end, i dont want see my members moving away... Crying or Very sad


Thats a feature our lecturer "adviced" us to add, we didn find it useful either.

The only "feature" we came up on top of this implementation is that our design only allows friends to send message to a member. With "1-way" relationship when A deleted B from his friendlist, B will not be able to send message to A; on the other hand if B decided not to "break" his/her relationship with A, A will still be able to send message to B. Come to think of it...implementing a "allowed list" will be more applicable. -..-

PS: In my opinion not allowing non-friends to send messages to each other may not be suitable for a social networking service like friendster though. It fits our design because we were implementing something like a "closed network", phobiting non-friends to send message to the member, thus preventing spams.
kawkazEE
cool! k tnx! ill let you guys know if i encounter another problem... its hard coz im this alone... but i enjoy it! it gives me experience that i didnt earn in my academics... hehehe Twisted Evil
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2007 Frihost, forums powered by phpBB.