Friends list: relational database table design

Question

So, the modern concept of the list of friends:
Say we have a table called "Man." Now this Person should have many friends (of which each friend is also in the person’s class). The most obvious way to build relationships is through the connection table. i.e.

buddyID   person1_id   person2_id
0         1            2
1         3            6

But when the user wants to see the list of his friends, the program will have to check the columns "person1_id" and "person2_id" to find all his friends.

Is this a suitable way to implement this type of table, or would it be better to add a record twice ... i.e.

buddyID   person1_id   person2_id
0         1            2
1         2            1

So you need to search for only one column.

Thanks in advance.

Answer 1

-, .

create table Person (
   person_id int not null primary key,
   username varchar(100) not null,
   ... other_cols ...
)


create table Buddy (
   person_id1 int not null,
   person_id2 int not null,
   primary key (person_id1, person_id2),
   foreign key (person_id1) reference Person (person_id),
   foreign key (person_id2) reference Person (person_id)
)

, Person 1 . , . Buddy .

, , - Person:

person_id    username
1            George
2            Henry
3            Jody
4            Cara

- , :

person_id1   person_id2
2            4
1            4

, , , . , , , , , , :

person_id1   person_id2
2            4
4            2
1            4

4 1 , . . , Person. , . , , ( ) , .

:

Cara ( , ):

select count(*) from Person 
                join Buddy on person_id = person_id1 or person_id = person_id2
 where name = 'Cara'

, , :

person_id   considers_as_buddy_id
2           4
4           2
1           4
4           3

select count(*) from Person P
                join Buddy B on P.person_id = B.person_id
 where name = 'Cara'

, . 2. - , , :

select count(*) from Person P
                join Buddy B on P.person_id = B.person_id and 
                                B.considers_as_buddy_id = P.person_id
 where name = 'Cara'

Answer 2

.

-, , . , ? , , .

Commonsense , . , . : A B B A. . , , , .