Everah wrote:You know, there is no reason why you can't use the same table setup proposed. The only change is that you would include reciprocated relationships. So that if I added you as a friend, it would show you as a frined on my list, but not vice versa until you added me to your friends list. Then we would be 'related'. Still possible in my opinion.
Exactly - the table setup works fine as long as the relationships are NOT reciprocal, and that UserA is friends with UserB does NOT imply that UserB is friends with UserA.
In this case the table would contain one entry for UserA - UserB relationship, and another entry for UserB - UserA relationship.
If however, there is only one entry for the relationship (bad idea in my view), there would also need to be a couple of flag fields (which would say in effect who is friends with who).
On the second post, about trying to write SQL to find the path from A to B where A and B are not friends, I'm pretty much convinced that it can't be done with SQL. I once built a Private Messaging system, where I wanted to be able to show the user the whole history of the PM, ie: The first message, the response to that message, the response to the response etc. etc. and found that SQL would only take me back one level per table join (I was joining the table with itself). Since I had no way of knowing how many messages were in the history, I couldn't do it wth simple SQL, and had to use a recursive function (similar to one that parses a filesystem).