Page 1 of 3

friendlist database structure...solved

Posted: Mon Sep 07, 2009 7:39 pm
by scarface222
Hey guys I just was wondering if anyone could give me suggestions on the structure of a friends database structure. What I am thinking of right now sounds inefficient. I want to create 3 columns, user1, user2, and status. The status can be 'pending', 'friends', or 'denied'. The problem with this structure is that in order to show a user's friends, I will have to do two queries: 1 to check user1 column and 1 for user2 column depending on who added who, which just seems silly and will get complicated. Also, to check if someone has requests I will also need two queries. I was just wondering if anyone had a better suggestion.

Re: friendlist database structure...any suggestions appreciated

Posted: Mon Sep 07, 2009 7:58 pm
by John Cartwright
The table structure you breifly described sounds appropriate. It doesn't sound like you'll need to perform multiple queries either.

Perhaps something like this to fetch the sender, recipient, and status.

Code: Select all

SELECT `u1`.`username` AS sender, `u2`.`username` AS recipient, `fr`.`status`
FROM `friendrequests` AS `fr` 
INNER JOIN friends AS `u1` ON `fr`.`user1` = `u1`.`id`
INNER JOIN friends AS `u2` ON `fr`.`user2` = `u2`.`id`
..

Re: friendlist database structure...any suggestions appreciated

Posted: Mon Sep 07, 2009 8:09 pm
by scarface222
For example to display user friends or check requests would be done in a similar manner. Just was wondering if there was a more efficient way lol since I am kind of amateur and just want to make sure.

Code: Select all

$friends="SELECT * FROM friends WHERE user1='$username' AND status='friends'";
$query=mysql_query($friends);
 
while ($row = mysql_fetch_assoc($query)) {
    echo $friends=$row['user2'];
}
 
$friends="SELECT * FROM friends WHERE user2='$username' AND status='friends'";
$query=mysql_query($friends);
 
while ($row = mysql_fetch_assoc($query)) {
    echo $friends=$row['user1'];
}

Re: friendlist database structure...any suggestions appreciated

Posted: Mon Sep 07, 2009 8:12 pm
by scarface222
I just saw your queries, that looks like it would cover the friend request but I mean for the friend table itself. Should I use 2 or like the way I said it first? Also, what were you doing with the innerjoin and 2 letter representations, I don't fully understand all of it.

Re: friendlist database structure...any suggestions appreciated

Posted: Mon Sep 07, 2009 9:17 pm
by superdezign
You are right, that is inefficient. You shouldn't need joins or multiple queries. Both take more database power than is necessary.
However, the database structure is efficient. Friendships are an item, and only 1 table is needed for one type of item.

The best way to do it is by structuring your table this way:

Code: Select all

CREATE TABLE `friendships` (
  `user_id` int NOT NULL,
  `friend_id` int NOT NULL,
  `status` int,
UNIQUE(`user_id`, `friend_id`));
A friendship is a user and a friend. Instead of querying both the `user` and the `friend` column, only query the `user` column. This way you can get a user's friends simply by checking their id against the table.

This is possible if, instead of adding complexity to your query, add complexity to your table. Every friendship has 2 entries in the table. So if user #1 is friends with user #2, there will be an entry of `user_id`=1 & `friend_id`=2, as well as `user_id`=2 & `friend_id`=1. This way, you have 2 INSERT statements once instead of multiple JOIN statements every time you load a user's friends list.

Constants are more efficient than variables. Complexity 1 time is better than complexity x times.

Re: friendlist database structure...any suggestions appreciated

Posted: Mon Sep 07, 2009 10:54 pm
by scarface222
Thanks a lot for your response. You had some interesting and informative things to say. I just have some questions.

1. Are you suggesting to insert each entry twice for each user's friend (like the way you desribed, just switch the user and friend id)?

2. Should then a variation of my previously suggested mysql statements be used (the ones by me, two for the friends list)?

3. What implications would you suggest for the status entry? For example, let status id of 1=pending 2=friends 3=denied. Lets say user id of 1 makes a request for user id of 2. 2 entries go into the database as 1,2,1 and 2,1,1 (by heading user_id,friend_id,status_id). How should I query to distinguish who has made the request and who is receiving? Both users by the database are simply shown as pending (1 person has to accept the other invite).

Re: friendlist database structure...any suggestions appreciated

Posted: Tue Sep 08, 2009 2:04 pm
by scarface222
superdezign?

Re: friendlist database structure...any suggestions appreciated

Posted: Tue Sep 08, 2009 8:39 pm
by scarface222
anyone know lol?

Re: friendlist database structure...any suggestions appreciated

Posted: Tue Sep 08, 2009 10:00 pm
by ell0bo
scarface222 wrote: 1. Are you suggesting to insert each entry twice for each user's friend (like the way you desribed, just switch the user and friend id)?
I believe he is yes, and that's what I would recommend.
scarface222 wrote: 2. Should then a variation of my previously suggested mysql statements be used (the ones by me, two for the friends list)?
Depends on how you design your table, depending on what you do for #3
scarface222 wrote: 3. What implications would you suggest for the status entry? For example, let status id of 1=pending 2=friends 3=denied. Lets say user id of 1 makes a request for user id of 2. 2 entries go into the database as 1,2,1 and 2,1,1 (by heading user_id,friend_id,status_id). How should I query to distinguish who has made the request and who is receiving? Both users by the database are simply shown as pending (1 person has to accept the other invite).
You will need a few more statuses then that, mainly for if someone decides they no longer want to be friends. Also, if you're doing this in MySQL (maybe some other db's) don't use a trigger to change the other person's status. You can only change one row of a table per transaction (annoying as hell sometimes). You will have to perform two action, one for the person removing the friend, and one for the friend getting moves (say one goes to status 4 - I hate you now - and the other goes to 5 - why don't you love me anymore).

that makes pulling up active friends easy WHERE user_key = ? and status_key = 3. That will get you everyone he's friends with.

Re: friendlist database structure...any suggestions appreciated

Posted: Wed Sep 09, 2009 2:36 pm
by scarface222
Thanks for the response man, then what would you suggest for the request system so the request is differentiated for who is sending and receiving.

Re: friendlist database structure...any suggestions appreciated

Posted: Wed Sep 09, 2009 8:58 pm
by scarface222
anyone else have any idea how to accomplish this?

Re: friendlist database structure...any suggestions appreciated

Posted: Thu Sep 10, 2009 6:18 pm
by scarface222
going once?

Re: friendlist database structure...any suggestions appreciated

Posted: Thu Sep 10, 2009 6:37 pm
by John Cartwright
Please stop bumping within the last 24 hours of the last post.. Thanks.

Re: friendlist database structure...any suggestions appreciated

Posted: Sun Sep 13, 2009 10:10 am
by superdezign
scarface222 wrote:Thanks for the response man, then what would you suggest for the request system so the request is differentiated for who is sending and receiving.
Personally, I find it is better to remove the `status` field completely. Friendships and friend requests are two different objects and should be in different tables.

Code: Select all

CREATE TABLE `friendships` (
  `user_id` int NOT NULL,
  `friend_id` int NOT NULL,
UNIQUE(`user_id`, `friend_id`));
 
CREATE TABLE `friend_requests` (
  `requester_id` int NOT NULL,
  `requestee_id` int NOT NULL,
UNIQUE(`requester_id`, `requestee_id`));
This also gives you the ability to automatically build a friendship if, coincidentally, both users create friend requests for each other without actually checking their friend requests. You would do this by checking for a vice-versa of their friend request object before inputting it into the table.

You would get a user's friends list from the friendships table, and their friend requests from the friend_requests table.

Re: friendlist database structure...any suggestions appreciated

Posted: Sun Sep 13, 2009 4:31 pm
by scarface222
That sounds like an efficient structure. For blocking someone I guess it would make sense then to just add another table with user_id and block_id like the previous ones you suggested. Also for the mysql statement would it be most efficient to query twice like the ones I wrote early in this topic to check for friends? Thanks again, Appreciate your help man. I thought this topic was a lost cause when that guy told me to stop bumping lol.