DB design for friends network
 

I am building a site for which I would like to add a "friends" invitation system. Once member invites another to be a friend, the second member has to accept to become friends. So both members have to agree to be friends.

My problem is that I can't think of a db schema that will allow me to do this without being redundant or requiring multipe requests to get a list of friends.

This is what I've come up with so far:

Table: Users
- userID
- (etc)

Table: Friends
- fromID
- toID

So if user1 invites user2, there is an entry in Friends where fromID=user1 and toID=user2. When user2 accepts, an entry is added to Friends where fromID=user2 and toID=user2. Now that they've both "invited" each other, they are friends. But now I have two rows for every friend relationship and have to do two queries to see who user1's friends are.

I'm a decent programmer and a good UI designer, but a novice db designer at best. I could use some recommendations. Thanks.

Re: DB design for friends network
 

yes, right logic. Also you can add a field in Users table to add friends separated by comma, you could use then explode function to separate the array and find again each user to find correlations. I think your solution is the best choice.