I am writing a learning application. I can not understand how I keep in the database of friends, as in contact.

I have a table that contains the user name, his ID, and other information, but does not contain information about who is friends with him.

I wanted to ask you how I can structure my database so that I can get to know friends of a certain user.

I have only one solution, but, in my opinion, it is very bad: create the exact same table, and then connect these tables through auxiliary with the help of a foreign key.

    3 answers 3

    A copy of the table is not needed. In the "auxiliary" table, you can create two foreign keys to the same table. Total

    create table tesе(id1 int Not null REFERENCES users(id_ user), id2 int not null REFERENCES users(id_user));
    • Mysql does not allow me to do this, I write something like this create table teste (id1 int Not null, FOREIGN KEY (id1) REFERENCES users (id_ user), id2 int not null, FOREIGN KEY (id2) REFERENCES users (id_user)); mysql swears at the second foreign key - qwas13
    • Thank you, everything worked - qwas13
    • Well and good. - msi

    In general, it is better to create a friends table, make the fields id | user_id | friend_id | status There is no need to explain anything in principle, in the status field if 1, then this is a confirmed application, if 0, then another request has not yet been confirmed

    • but it turns out that you need to either copy the lines, or the user has one friend - qwas13
    • one
      in the sense of? cycle run and count the user's friends where status = 1 and user_id = equal to your id - Pavel Dura
    • If 1 | 2 | 1 1 | 3 | 1 1 | 4 | 0 In this case, it will mean that the user with ID = 1 has two confirmed friends with ID = 2 and 3. And one friend is not confirmed and is waiting for confirmation. - Yoharny Babay
    • Actually, at @ qwas13 this is called the "auxiliary table". :-) And we all talk about the same thing. - msi
    • Otherwise, in any case, even with a large size, I need to cache ... - Yoharny Babai
     table `friends` ( ... `user_id`, `friend_id` )