Modeling a friend system database

Question

Modeling a friend system database

Navigation

#1 by (2 votes)
#2 by (0 votes)

3

Hello, I'm developing an application where there will be an option to add friends, it's very simple, it's not social network, but you have to add a friend. I have the user table. Which tables and which relationships do I have to create to add friends? If someone sends you a simpler example that works, show which tables, and which fields, and which relationships one needs. Thanks

I found an example, I think it solves my situation:

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

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

3) Mary refuses - Deletes the record from the REQUISITION table.

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

But how would the types of relationships be, which way?

mysql modelagem modelo-relacional uml

asked by anonymous 10.11.2014 / 18:18

2 answers

0

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

select * from friends where userid = X

| friends |

10.11.2014 / 18:38

Can not use tskill in C # How do I make a ListView update a title at the top, as I scroll the scroll?

score 2 · Accepted Answer

In my view it is interesting to store the requestor and requested as follows

        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 handle serial requests

That way, you could fetch all friends from 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 that a user has pending to approve

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

Note

In terms of performance sometimes it is better not to use the control by date or null field, but by an indexed boolean depending on which engine will be used to access MySql. In this case it would be nice to have a field boolean confirmado separated from the date and indexed by it

Having a rejection flag or rejection date is a simple and objective alternative, as well as 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, 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