tyleradam
asked on
Database setup for "Friends network". What's the best way?
I have a member's website (PHP/MySQL) that I am setting up a friends networking section on and I wanted to know if anyone has any reccomendations on the best way to do this for optimal performance?
How it would work: Members can send out friend requests to other members. And then the other member can accept or deny the request. Then when you go to a member's profile page you see all their friends. Kind of like a MySpace deal. Then if either friend wishes to have the other removed, the deletion occurs on both ends.
So right now I'm thinking of doing something like this for the friends table:
FRIENDS
fid
f1
f2
status
Where 'fid' is an auto incremented primary key, 'f1' and 'f2' are the member IDs and 'status' will be either 0 or 1. 0 for unapproved and 1 for approved.
But then I have a feeling this table will get HUGE! Right now i have 20,000 members. So that's a possisbility of having 20,000 ^ 20,000 rows? So would that be the best way to setup the table or is there a better more optimal solution that I'm missing.
If not, then what would be the best way to query this to find the friends to display on the profile pages? This is what I got so far...
<?php
$query = mysql_query ("SELECT f1, f2, fid FROM friends WHERE (f1 = '$id' OR f2 = '$id') AND status = '1' ORDER BY fid");
while($row = mysql_fetch_array ($query)){
$f1 = $row["f1"];
$f2 = $row["f2"];
$fid = $row['fid'];
if($f1==$id)
{
$f=$f1;
}else{
$f=$f2;
}
?>
How it would work: Members can send out friend requests to other members. And then the other member can accept or deny the request. Then when you go to a member's profile page you see all their friends. Kind of like a MySpace deal. Then if either friend wishes to have the other removed, the deletion occurs on both ends.
So right now I'm thinking of doing something like this for the friends table:
FRIENDS
fid
f1
f2
status
Where 'fid' is an auto incremented primary key, 'f1' and 'f2' are the member IDs and 'status' will be either 0 or 1. 0 for unapproved and 1 for approved.
But then I have a feeling this table will get HUGE! Right now i have 20,000 members. So that's a possisbility of having 20,000 ^ 20,000 rows? So would that be the best way to setup the table or is there a better more optimal solution that I'm missing.
If not, then what would be the best way to query this to find the friends to display on the profile pages? This is what I got so far...
<?php
$query = mysql_query ("SELECT f1, f2, fid FROM friends WHERE (f1 = '$id' OR f2 = '$id') AND status = '1' ORDER BY fid");
while($row = mysql_fetch_array ($query)){
$f1 = $row["f1"];
$f2 = $row["f2"];
$fid = $row['fid'];
if($f1==$id)
{
$f=$f1;
}else{
$f=$f2;
}
?>
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
snoyes_jw
It wouldn't be 20,000 ^ 20,000, because you don't need friends to appear in both f1 and f2 - that is, if Bob and Sue are friends, you don't need both rows Bob / Sue and Sue / Bob; either one will suffice. Therefore, at most you'd have 19,999 * 10,000 =~ 200 million rows. Still a lot of rows, but what are the odds of everybody on the site being friends with everybody else?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Cool. Thanks for the help. I have one more question to add to this. Should I just ask it here or start a new thing? I'm new to this. Heh.
It's regarding the table setup. Do I even need to keep the 'fid' auto-incremented primary key field? I mean I don't use it anywhere I think. When someone deletes a friend. It doesnt delete by the fid but by the f1 and f2 relationship. So, it it just a waste of space.. or is it good to keep around for other reasons?
It's regarding the table setup. Do I even need to keep the 'fid' auto-incremented primary key field? I mean I don't use it anywhere I think. When someone deletes a friend. It doesnt delete by the fid but by the f1 and f2 relationship. So, it it just a waste of space.. or is it good to keep around for other reasons?
I you're not using it, you can delete it.
You can make new primary key consisting of columns f1 and f2 to avoid inserting duplicate relations into the table
my advice is to keep the fid field. There's no reason to relate your key so directly to f1 and f2. unique fields provide structure.