friendlist database structure...solved
Moderator: General Moderators
-
scarface222
- Forum Contributor
- Posts: 354
- Joined: Thu Mar 26, 2009 8:16 pm
friendlist database structure...solved
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.
Last edited by scarface222 on Tue Sep 29, 2009 8:57 pm, edited 2 times in total.
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: friendlist database structure...any suggestions appreciated
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.
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`
..-
scarface222
- Forum Contributor
- Posts: 354
- Joined: Thu Mar 26, 2009 8:16 pm
Re: friendlist database structure...any suggestions appreciated
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'];
}-
scarface222
- Forum Contributor
- Posts: 354
- Joined: Thu Mar 26, 2009 8:16 pm
Re: friendlist database structure...any suggestions appreciated
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.
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Re: friendlist database structure...any suggestions appreciated
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:
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.
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`));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.
-
scarface222
- Forum Contributor
- Posts: 354
- Joined: Thu Mar 26, 2009 8:16 pm
Re: friendlist database structure...any suggestions appreciated
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).
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).
-
scarface222
- Forum Contributor
- Posts: 354
- Joined: Thu Mar 26, 2009 8:16 pm
-
scarface222
- Forum Contributor
- Posts: 354
- Joined: Thu Mar 26, 2009 8:16 pm
Re: friendlist database structure...any suggestions appreciated
anyone know lol?
Re: friendlist database structure...any suggestions appreciated
I believe he is yes, and that's what I would recommend.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)?
Depends on how you design your table, depending on what you do for #3scarface222 wrote: 2. Should then a variation of my previously suggested mysql statements be used (the ones by me, two for the friends list)?
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).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).
that makes pulling up active friends easy WHERE user_key = ? and status_key = 3. That will get you everyone he's friends with.
-
scarface222
- Forum Contributor
- Posts: 354
- Joined: Thu Mar 26, 2009 8:16 pm
Re: friendlist database structure...any suggestions appreciated
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.
-
scarface222
- Forum Contributor
- Posts: 354
- Joined: Thu Mar 26, 2009 8:16 pm
Re: friendlist database structure...any suggestions appreciated
anyone else have any idea how to accomplish this?
-
scarface222
- Forum Contributor
- Posts: 354
- Joined: Thu Mar 26, 2009 8:16 pm
- John Cartwright
- Site Admin
- Posts: 11470
- Joined: Tue Dec 23, 2003 2:10 am
- Location: Toronto
- Contact:
Re: friendlist database structure...any suggestions appreciated
Please stop bumping within the last 24 hours of the last post.. Thanks.
- superdezign
- DevNet Master
- Posts: 4135
- Joined: Sat Jan 20, 2007 11:06 pm
Re: friendlist database structure...any suggestions appreciated
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.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.
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`));You would get a user's friends list from the friendships table, and their friend requests from the friend_requests table.
-
scarface222
- Forum Contributor
- Posts: 354
- Joined: Thu Mar 26, 2009 8:16 pm
Re: friendlist database structure...any suggestions appreciated
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.