Modeling database of a system of friends

Question

Hello, I am developing an application where there will be an option to add friends,is very simple, it is not social network, but have to add friend.
I have the user table.
What tables and relationships do I have to create to add friends? If someone sends a simpler, working example, show which tables, which fields, and which relationships they need. Thank you

I found an example,:

1) John makes a request for friendship for Mary - requestorId = id of Joao - targetId = id of Maria - Create record in REQUISICAO table (requestorId, targetId)

2) List the requests sent to Maria on her page - Fetch all records in the REQUISICAO table when targetId is equal to Maria’s id - For each record assemble a message of the type (Joao wants to be your friend - Accept or not)

3) Mary refuses to - Excludes the record from the REQUISICAO table.

4) Mary accepts - Create record in FRIENDSHIP table (idJoao, idMaria) - Excludes the record from the REQUISICAO table.

But what would the types of relationship look like, which?

Answer 1

In my opinion it is interesting to store the applicant and the requested in the following way

        Usuarios
----------------------------
id_usuario  |  Nome
    1       |  Joao
    2       |  Maria
    3       |  José

                                 Amigos
-------------------------------------------------------------------------
data_solicitadao | id_solicitante | id_solicitado | data_confirmacao
  01/11/2014     |      1         |     2         |     NULL
  01/11/2014     |      2         |     3         |     01/11/2014
  01/11/2014     |      1         |     3         |     02/11/2014
  02/11/2014     |      2         |     1         |     NULL

I would still include if there was refusal of friendship to control requests in series

That way, it would be possible to get all the friends of a particular user

SELECT * 
FROM amigos 
WHERE 
  id_solicitante = :id_usuario 
  OR id_solicitado = :id_usuario 
  AND data_confirmacao  IS NOT NULL

And all the requests a user has pending to approve

SELECT * 
FROM amigos 
WHERE 
  id_solicitado = :id_usuario 
  AND data_confirmacao IS NULL

---

Observation

In terms of performance it is sometimes best not to use the control by date or null field but by an indexed Boolean depending on which engine to use to access Mysql. Then it would be nice to have a field boolean confirmado separated from the date and indexed by the date

Having a rejection flag or rejection date is a simple and objective alternative, and optimized to query rejected requests

-------------------------------------------------------------------------------------
data_solicitadao | id_solicitante|id_solicitado|data_confirmacao|aprovado|rejeitado
  01/11/2014     |      1        |    2        |    NULL        |  False |  False
  01/11/2014     |      2        |    3        |    01/11/2014  |  True  |  False
  01/11/2014     |      1        |    3        |    02/11/2014  |  True  |  False
  02/11/2014     |      2        |    1        |    NULL        |  False |  True

New proposal

Use a single field with the current status of the request (P Pending, A approved R Rejected)

-------------------------------------------------------------------------------------
data_solicitadao | id_solicitante|id_solicitado|data_confirmacao| situacao
  01/11/2014     |      1        |    2        |    NULL        |    P
  01/11/2014     |      2        |    3        |    01/11/2014  |    A
  01/11/2014     |      1        |    3        |    02/11/2014  |    R
  02/11/2014     |      2        |    1        |    NULL        |    P

Answer 2

Simple, you would have a table called friends that would contain the user id and the friend id, it would be 1 - n and in each record you would put the user id and the friend id so if you want to search for a user’s friends would make the following sql:

select * from friends Where id_usuario = X

| friends |

| id | | id_usuario| | id_friend |