Solved

Social network friends table structure & functions advice - MySQL/PHP

Posted on 2010-11-29
19
1,964 Views
Last Modified: 2012-05-10
Hello,
I am building a sort of social network where I have the ability to have friends, and I would like to hear your feedback on my implementation and if you suggest any better methods

I already have a table 'friendships' as such:
friendship_id 
requester_user_id
receiver_user_id
status (1 if accepted by receiver_user_id, 0 if friendship is still pending)
friend_request_timestamp
friend_acknowledge_timestamp

Open in new window


now my main issue is in getting the list of friends of someone, let's assume the table is populated as such (and all are approved friendships):
requester_user_id  = 1 ; receiver_user_id = 2
requester_user_id  = 1 ; receiver_user_id = 3
requester_user_id  = 4 ; receiver_user_id = 1
requester_user_id  = 5 ; receiver_user_id = 1

Open in new window


in case I want to retrieve the friends of user_id 1, I can typically do:
SELECT * FROM friendships WHERE requester_user_id = 1 OR receiver_user_id = 1  (and then join this with users table)

but the results are not practical, I want the return column to just include the friend ids, so I tried this:
SELECT friendships.requester_user_id AS friend_id, users.* FROM friendships,users WHERE receiver_user_id = 1 AND friendships.requester_user_id = users.user_id
UNION 
SELECT friendships.receiver_user_id AS friend_id, users.*  FROM friendships,users WHERE requester_user_id = 1 AND friendships.receiver_user_id = users.user_id

Open in new window



that returns values correctly and as I wish, but is it the optimum way to do it ? or is there any better way ?
it looks like a big heavy query to me, doing 1 union on 2 joins

so I would like to hear your feedback on such a method, and what would the best way be

I currently use the 'status' field to declare whether the receiver of the request accepted it or not, ignored it, or blocked the person (the value of status would be as such ->  0 = pending, 1 = accepted, delete row if ignored, blocked = 2)

I have timestamps to show when the request was sent, and when approved

I would like to also be able to implement something like the 'mutual friends' on facebook,
any ideas on that? is there a nice sql query that would do that ...I am thinking of:
- select friends of first user and put them in an array
- select friends of second user where friend_id "IN"  the above array
- join the result with the users table

but that seems quite heavy, is it better to process through PHP ?

thanks all! :)
0
Comment
Question by:roynaufal
  • 9
  • 5
  • 5
19 Comments
 
LVL 19

Accepted Solution

by:
Bardobrave earned 250 total points
ID: 34229375
Maybe your friendship table should not make any reference to whom is the friendship starter. If your table states the relationship this way, you'll have two records for each friendship:

A is friend of B
B is friend of A

Your friends list for X user then always will  look for ids in the second field of the relationship, as X user is in the first field.

You'll have double number of records on your table, but your queries will be a lot less consuming. You'll also able to follow relations where A is friend of B but B is not friend of A (if it's something you need to do).

I supose that here you'll think in your model and decide wich system is better to your environment. If you expect to have thousands of friendships with not many queries to friendship lists your current design is better than my suggested approach, however, if you expect to have a sustantially bigger number of queries to frienship than your friendships total probably this approach would be better.
0
 
LVL 3

Author Comment

by:roynaufal
ID: 34229421
actually the friendship is always 2 sided, for A to be friend with B, then B is also friend for A
and in all cases, friendship is always 1 row for the same 2 people (so no duplicated issue)...if A requests friendship from B, then B can no longer request friendship from A (they are already friends)

I do get what you are saying though, but assuming I go with that approach, how would I proceed to know who's A and who's B ?
for example, X user has Y & Z as friends
but A has X as friend too

so X's friends as Y, Z, and A
if I had X always on 1 side, then A would not show up in the friend's list


in my current model, A is the person who requests friendship from B, if it is approved, then the status is turned to '1' and they are friends, otherwise the row is deleted (in case of ignore) or status set to 2 in case B blocked A (or I might possibly put the blocks in another table as well, which I think I will)


to note, I will be having alot of queries on the number of friends the user has (not necessarily the info of the friends though), a count query

thanks!
0
 
LVL 19

Expert Comment

by:Bardobrave
ID: 34229452
In the approach I suggest, if A is friend of X there will be the following two records:

X is friend of A
A is friend of X

So your problem will be to create a second record when friendship is stablished.

In my suggested approach your friendship table represents a one to many relationship between users so for each user you have a list of its friends. No more requester and accepter, they will be user and friends. So your first field always represents the user you are asking for and the second one the list of it's friends.

In this case, your code will be responsible of stablish the dual relationship, creating a record for A is friend of X when X becomes friend of A.
0
 
LVL 19

Expert Comment

by:Bardobrave
ID: 34229455
You could separate friendship relationship and friendship request.

You can have one table with friendship requests, as you have now. But when a friendship request is accepted, you trigger the creation of two records on your friendhisp table (A -> B and B -> A) and the delete of the request.
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 34229553
@roynaufal, have you really been a limited member for over two years?  C'mon and join!

Each user has a one-to-many relationship with two data elements here -- Requests and Friendships.  I would put these into different tables.  Friendships would be defined in a "pivot table" which is only two columns wide; it carries the keys of the two users who are friends.  You could add more stuff to the table, but that is all that is required for functionality.  

Requests are the same, but you might want to keep a little more information.  You carry the key of the user who made the Request and the key of the object of her affections.  When the Request is agreed to, you copy these two keys from Requests to Friendships.  When the Request is declined, you delete the row from Requests.  Of course, you could keep the row around, with a "declined" status, if you wanted to keep track of that sort of thing.  The direction of the request is not important after Friendship has been established, but it could still be carried.

That's the basics.  Adding some ideas here, you might keep a DATETIME value of when the Request was made, which requests were ignored (but not declined), etc.  And you would want some kind of garbage collection to remove rows from these tables when a user drops out of the community.
0
 
LVL 3

Author Comment

by:roynaufal
ID: 34229607
hehe :P I only use the site like once every few months, but I'm changing a bit of directions in my business plans, so I might actually join soon!

anyway,
hmm,
following your model for the friendship relations,
how do you store the data?
for example which of these situations would be better considering a heavy loaded site:

* store records as A friends with B, B friends with A ... and if I need friends of A, I would then query all cases of where the 1st column is A
or
* store records as A friends with B only, and then use more complicated queries like the ones mentioned in my initial post ?

guys, thanks alot for the insight !
0
 
LVL 19

Expert Comment

by:Bardobrave
ID: 34229694
As I stated previously I think it would depend on the number of expected friendships / number of expected queries ratio.

An approach where relationships are doubled will supose twice as info in your friendship table, wich can be a problem if you expect to have a wide number of users and relations. But, if you are going to have a big number of users/friendships and for each one of them you expect a heavy use of the app with lots of accesses to friend's lists the complexity of queries would finish being a truly mess.

For a site with few hundreds/thousands of users I think it would be better the A->B->A approach. For a site with hundreds of thousands, or even millions of users more things should be taken in consideration, as server memory, hit balancing, expected usage, etc...
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 34230004
If A is a friend of B, it would seem to follow that B is a friend of A.  At least that has worked well for Facebook.  So you don't need anything more than the two-column pivot table to establish that relationship.
0
 
LVL 3

Author Comment

by:roynaufal
ID: 34230063
@ray_paseur,
do you find storing 2 records for each friendship is my best best solution as was suggested?

on 1-2 years, expectations are tens of thousands of users,
later phase would become a different case and scalability would be checked then
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 34230647
A friendship is a one-to-one relationship.  Each user may have many friends, so this is a zeroe-to-many relationship.  So, I think the answer is "one record" for each friendship.  Example:

User A is friends with user B
User A is friends with user C

Table of Friendships contains
A:B
A:C

Thus the table (which will be indexed on both columns) will be as large as the number of friendships, and that may sound very big.  But because the information in the table is only two integers per row, and because the table is well-indexed it will provide a very fast lookup.  You want to know if A is friends with C?  The query looks like this:

SELECT id1 FROM friendships WHERE (id1='A' AND id2='C') OR (id2='A' AND id1='C') LIMIT 1

Does that make sense to you?
0
 
LVL 3

Author Comment

by:roynaufal
ID: 34231148
yea, that was what I was initially thinking at first, but then it was suggested I do 2 rows for A:B and B:A , which got me a bit wondering which is actually better to do, eventhough my mind tells me logically 1 row for A:B relation should be enough

my main issue was a bit with the query where let's say i wanted to get all friends of A,
which is as simple as:
SELECT user_id1, user_id2 FROM friendships WHERE (user_id1 = 'A') OR ( user_id2 = 'A')

but the friends I want will be mixed in 2 separate columns, some friends as user_id1 and others as user_id2,
and I then want to join those ids with the users table,
my best solution was the below query which unifies all friends in 1 column, but it looks 'heavy', so:
- can it be optimized further?
- or is 2 records for each friendship (A:B, B:A) a better way to do it (more records but faster for sql?)



SELECT friendships.user_id2 AS friend_id, users.* FROM friendships,users WHERE user_id1 = 'A' AND friendships.user_id2 = users.user_id
UNION
SELECT friendships.user_id1 AS friend_id, users.* FROM friendships,users WHERE user_id2 = 'A' AND friendships.user_id1 = users.user_id
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 34231335
I'd just be speculating about the speed of a query, but I don't think you will find much difference between the SELECT UNION and just doing two queries.  The programming will be simpler with SELECT UNION.  The use of 2 records for each friendship makes sense if you don't mind the table maintenance when B decides to unfriend A, but A has not decided to unfriend B.
0
 
LVL 3

Author Comment

by:roynaufal
ID: 34231452
doing very rough comparisons on my pc, the long union/join query is visibly slower, I have  very few test records...so not sure how that would be on a bigger scale - it's still not slow (still in fractions of fractions, but there's a 10th difference)
if A unfriends B, then B automatically unfriends A...the site is always based on a 2 sided relationship

I'm not sure if it is an advantage to use 2 records for each friendship with simpler queries,
or simply use a UNION query...which is better, I'm not sure...that's where I'm still confused

in other words, the question would be:
- is X records selected with a union query like the above better than:
- 2X records selected with a simpler query?
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 34235511
I've just got to sign off on this question with a practical answer.  When you get to 10,000 clients, hire a developer to help.  If you want concrete advice at this point in the app lifecycle, hire a DBA to give you a couple of hours of advice.  There are just too many "squishy" parts of the question to render a competent answer.  I'll bet in your current tests the timing differences are measured in milliseconds.  So as you scale up you may see differences that extend into seconds.  No big deal, if the value proposition of your web site is good enough.

Best of luck with it, ~Ray
0
 
LVL 3

Author Comment

by:roynaufal
ID: 34246146
okay
thanks alot for your feedback, it gave me some good insight !

I was testing things around, I realized one new issue,
in case I do 1 row for each relation, and I do a SELECT query using UNION, I can no longer sort the results using order by
SELECT friendships.user_id2 AS friend_id, users.* FROM friendships,users WHERE user_id1 = 'A' AND friendships.user_id2 = users.user_id
UNION
SELECT friendships.user_id1 AS friend_id, users.* FROM friendships,users WHERE user_id2 = 'A' AND friendships.user_id1 = users.user_id

is there a workaround on that?
or should in that case going with  with Bardobov's suggestion is my best approach ?
0
 
LVL 3

Author Comment

by:roynaufal
ID: 34246152
sorry, bardobrave * typo!
0
 
LVL 19

Expert Comment

by:Bardobrave
ID: 34246375
You cannot order each select separatedly?

You always could make two different queries (each one with each part of the union sorted) and loop through both... a little bit more of processing.
0
 
LVL 3

Author Comment

by:roynaufal
ID: 34246436
yea I tried ordering each separately, doesn't work it seems, I go an sql error that union/order by cannot  be used together, which I found odd
I'll double check,maybe I missed something, I was working on this at 3am so.. lol
0
 
LVL 3

Author Comment

by:roynaufal
ID: 34247135
i restructured my sql after doing some reading, it now is:
SELECT f.friendship_timestamp, u.first_name, u.user_id AS friend_id, i.image_path
            FROM friendships             AS f
            LEFT JOIN users                  AS u       ON f.user_id1 = u.user_id
            LEFT JOIN user_images      AS i      ON u.user_id = i.user_id
            WHERE user_id2=1
UNION
SELECT f.friendship_timestamp, u.first_name, u.user_id AS friend_id, i.image_path
            FROM friendships             AS f
            LEFT JOIN users                  AS u       ON f.user_id2 = u.user_id
            LEFT JOIN user_images      AS i      ON u.user_id = i.user_id
            WHERE user_id1=1

 ORDER BY first_name, last_name

and works fine
thanks for all your help :)
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now