Suggestions for my database design (Networking Users on the website)

Question

Imagine a scenario where - All the users on the website can be affiliated to each other, i.e. something like be "friends" with each other and all. The Database i want to design should be able to handle connections like these - Suppose A is a friend of B (also implies B is a friend of A) wants to connect with D who is a friend of C who is a friend of B. Think social networking.

My problem being how do i store these list of affiliates in a table? What I've tried so far - Table 1 - Column 1 - User_id Column 2 - User_id of affiliate Having problems querying this table to give me a person's affiliates.

Table 2 Column 1 - User_id(A,B) Column 2 - User_id of affiliate(B,A) Each connection being shown by two rows instead of one, which makes querying easy.

But both of these seem too lame to me, do you guys know any better way of designing this kind of a database?

Answer 1

Create table 1.

Create a view over table 1 joining that table to itself to create table 2 as a view over table 1 with three columns.

Use both.