How to model n-to-n relationship correctly? Women also have friendship with men and not only men have friendship with women

Question

Hello, I want to do the following: Create in Mysql the n-to-n relationship where men have friendships with women and women also have friendships with men.

Example:

John has friendship with Fernanda, but, Fernanda has no friendship with John rather with Carlos, but John continues to have friendship with Fernanda.

I created a n-to-n model where men befriend women, but couldn’t make women befriend men and vice versa.

See my SQL script below:

create database pessoas;
use pessoas;

create table homens (id int auto_increment, nome varchar(10), primary key (id));

create table mulheres (id int auto_increment, nome varchar(10), primary key (id));

create table amizades (id int auto_increment, homem_id int, mulher_id int,
primary key(id),
foreign key(homem_id) references homens(id),
foreign key(mulher_id) references mulheres(id));

insert into homens values 
(default, 'Joao'),
(default, 'Flavio'),
(default, 'Carlos');

insert into mulheres values 
(default,'Ana'),
(default,'Fernanda'),
(default,'Julia');

insert into amizades values 
(default, 1, 2),
(default, 2, 1),
(default, 3, 3);

/* join com 3 tabelas, aparecer o id e nome do homem e depois o id e nome da mulher de quem o homem é amigo */
select homens.id, homens.nome, mulheres.id, mulheres.nome from homens join amizades on homens.id = amizades.homem_id
join mulheres on amizades.mulher_id = mulheres.id order by homens.id;

I want an explicit relationship in the database so I know who has friendship with whom and not just simulate that relationship by changing the order of the columns in the SELECT, understood? What better way to do this?

Answer 1

I would create two relationship tables: amizades_hm and amizades_mh. Both have only two fields: homem_id and mulher_id, both being part of the primary key in both tables:

Here is the script to create the database:

CREATE DATABASE pessoas;
USE pessoas;

CREATE table homens (
    id INT AUTO_INCREMENT,
    nome VARCHAR(10),
    PRIMARY KEY (id)
);

CREATE table mulheres (
    id INT AUTO_INCREMENT,
    nome VARCHAR(10),
    PRIMARY KEY (id)
);

CREATE table amizades_hm (
    homem_id INT,
    mulher_id INT,
    PRIMARY KEY (homem_id, mulher_id),
    FOREIGN KEY (homem_id) REFERENCES homens(id),
    FOREIGN KEY (mulher_id) REFERENCES mulheres(id)
);

CREATE table amizades_mh (
    homem_id INT,
    mulher_id INT,
    PRIMARY KEY (homem_id, mulher_id),
    FOREIGN KEY (homem_id) REFERENCES homens(id),
    FOREIGN KEY (mulher_id) REFERENCES mulheres(id)
);

To insert data into it:

INSERT INTO homens VALUES (DEFAULT, 'Joao'), (DEFAULT, 'Flavio'), (DEFAULT, 'Carlos');
INSERT INTO mulheres VALUES (DEFAULT, 'Ana'), (DEFAULT, 'Fernanda'), (DEFAULT, 'Julia');
INSERT INTO amizades_hm VALUES
    (1 /*Joao*/, 2 /*Fernanda*/),
    (2 /*Flavio*/, 1 /*Ana*/),
    (3 /*Carlos*/, 3 /*Julia*/);
INSERT INTO amizades_mh VALUES
    (2 /*Flavio*/, 2 /*Fernanda*/),
    (2 /*Flavio*/, 3 /*Julia*/),
    (3 /*Carlos*/, 3 /*Julia*/);

To read each man’s friends:

SELECT homens.id, homens.nome, mulheres.id, mulheres.nome
FROM homens
INNER JOIN amizades_hm ON homens.id = amizades_hm.homem_id
INNER JOIN mulheres ON amizades_hm.mulher_id = mulheres.id
ORDER BY homens.id;

Exit:

id | nome   | id | nome
---+--------+----+--------
1  | Joao   | 2  | Fenanda
2  | Flavio | 2  | Ana
3  | Carlos | 3  | Julia

To read each woman’s friends:

SELECT mulheres.id, mulheres.nome, homens.id, homens.nome
FROM mulheres
INNER JOIN amizades_mh ON mulheres.id = amizades_mh.mulher_id
INNER JOIN homens ON amizades_mh.homem_id = homens.id
ORDER BY mulheres.id;

Exit:

id | nome     | id | nome
---+----------+----+-------
1  | Fernanda | 2  | Flavio
3  | Julia    | 2  | Flavio
3  | Julia    | 3  | Carlos

To read all the friendships:

SELECT a.origem_tipo, a.origem_id, a.origem_nome, a.destino_tipo, a.destino_id, a.destino_nome
FROM (
    SELECT
        'Homem' AS origem_tipo,
        h1.id AS origem_id,
        h1.nome AS origem_nome,
        'Mulher' AS destino_tipo,
        m1.id AS destino_id,
        m1.nome AS destino_nome
    FROM homens h1
    INNER JOIN amizades_hm hm ON h1.id = hm.homem_id
    INNER JOIN mulheres m1 ON hm.mulher_id = m1.id
    UNION ALL
    (
        SELECT
            'Mulher' AS origem_tipo,
            m2.id AS origem_id,
            m2.nome AS origem_nome,
            'Homem' AS destino_tipo,
            h2.id AS destino_id,
            h2.nome AS destino_nome
        FROM mulheres m2
        INNER JOIN amizades_mh mh ON m2.id = mh.mulher_id
        INNER JOIN homens h2 ON mh.homem_id = h2.id
    )
) a
ORDER BY a.origem_tipo, a.origem_id;

Exit:

origem_tipo | origem_id | origem_nome | destino_tipo | destino_id | destino_nome
------------+-----------+-------------+--------------+------------+--------------
Homem       | 1         | Joao        | Mulher       | 2          | Fernanda
Homem       | 2         | Flavio      | Mulher       | 1          | Ana
Homem       | 3         | Carlos      | Mulher       | 3          | Julia
Mulher      | 2         | Fernanda    | Homem        | 2          | Flavio
Mulher      | 3         | Julia       | Homem        | 3          | Carlos
Mulher      | 3         | Julia       | Homem        | 2          | Flavio

Answer 2

An n-to-n relation is always modeled with an auxiliary table whose primary key is the set of the two foreign keys or a single key. Technically, your solution is correct.

First, the model in question does not prevent the relationship: "(...) where men have friendship with women, (...) women have friendship with men and vice versa." , since the combinations h-h, m-m, h-m, m-h are possible; although the codes coincide.

According to,

To get the intersection you can:

1)

select 
    homens.id, homens.nome, mulheres.id, mulheres.nome 
from amizades join homens on homens.id = amizades.homem_id
              join mulheres on mulheres.id = amizades.mulher_id;

2) Or,

select a.nome, b.nome
from
(select 
    homens.id, homens.nome
from amizades join homens on homens.id = amizades.homem_id) a
union
(select 
   mulheres.id, mulheres.nome 
from mulheres on mulheres.id = amizades.mulher_id) b

In none of the above cases you will need to simulate any relationship. I hope I have understood your doubt.