creating a friend list.. what way is most efficient?

[mikethecoder]

Okay so I have to create a decent sized virtual community online. Users will have profiles and can add other users on a "friend list". We all have seen this before. My question is what is the best way to program this in PHP? And also how should the database table for the friend list be structed? I was thinking have a table called friendlist and it would have a unique id for the primary key and then a field called "friendowner" and "slave" or something to that effect where both friends are foreign keys to the user table.

ID - Friendowner - Slave
1 - 5 - 43
1 - 5 - 54

So when the user whose id is 5 loads his profile it will show him having 2 friends whose id's are 43 and 54. But is this the best way to go? Lets say we have a site with 100k members and each member has 300 friends... that table would be huge. And the time it takes to search the table worries me. I suppose this is where caching could come in handy but still. Is there a more effective way? And could any type of indexing help?...I was thinking no since the fields will only consist of just numbers so its already sweet and to the point... just, a lot of data to sift through.

Please any help would be greatly appreciated!!! Thank you all!

[micky1955]

seems good one for use of database "relations" offered by mysql. two tables one for members and one for friends linked by their id numbers. that way you only have to store a member once and all his friends can be found by their friendowner's id.

good luck, Micky

[marcus]

You could do:

[code]
$sql = "SELECT * FROM `friends` WHERE id =$id LIMIT 12";
$res = mysql_query($sql) or die(mysql_error());

while($row = mysql_fetch_assoc($res)){
echo "<a href=profile.php?id=$row[friendowner]>$row[friendname]</a>|\n";
}
mysql_free_result($res);
[/code]

Then you could easily just make a new page like friends.php and use a get to show all the friends

[code]
$sql = "SELECT * FROM `friends` WHERE id =$_GET['id']";
$res = mysql_query($sql) or die(mysql_error());

while($row = mysql_fetch_assoc($res)){
echo "<a href=profile.php?id=$row[friendowner]>$row[friendname]</a>|\n";
}
mysql_free_result($res);
[/code]

[mikethecoder]

But is the database setup I showed efficient or is there a better way to create the database? I mean, what do some of the major sites out there like facebook use I wonder... The way I created the database with the friendowner and slave... if the site was to become huge... wouldnt that slow down everything scanning a table with hundreds of thousands of rows... one for each friend relationship. Im wondering if there is a better way to do this moreso in terms of the db setup versus accessing the data.

[entity]

That does seem like the best way of doing it. Maybe you could have pagination and a search function, so that the user can find their friend easier? Thats how I would probably go about it anyway.