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.