Início / dba / Perguntas / 335286
Accepted
leigero
Asked: 2024-01-30 00:38:33 +0800 CST

Como faço para construir um modelo de dados para uma grade de dados XY que consiste em muitos para muitos relacionamentos?

  • 772

Os dados

Digamos que eu tenha uma grade representando todas as pessoas em uma sala e as conversas que elas têm quando falam entre si.

John Susana Brian Rachel
John Resseguro Trabalhar Esportes Filmes
Susana Trabalhar Resmungando Viagem Música
Brian Esportes Viagem Nada Comida
Rachel Filmes Música Comida Auto-depreciativo

O banco de dados

Inicialmente considerei apenas fazer tabelas regulares de PeopleeConversations

A Conversationsmesa seria uma chave etopic participant1 participant2

O dilema

A ordem dos participantes é totalmente irrelevante. Uma simples pergunta “Sobre o que Rachel e Brian conversam” torna-se uma consulta complicada de executar.

Portanto, uma de duas coisas deve ser feita:

  1. Dados redundantes são adicionados ao banco de dados: (Rachel e Brian) ou (Brian e Rachel)
  2. Uma consulta ridiculamente complicada é necessária para consultar os dados não redundantes porque você não sabe se Rachel é participante1 ou participante2.

Seguir com 1 acima cria um modelo de banco de dados O (n ^ 2) insustentável, enquanto 2 parece um pesadelo de DBA tentando consultar dados significativos.

A questão

Como faço para modelar uma estrutura de dados como esta? O SQL é a ferramenta certa para o trabalho ou devo pesquisar algo totalmente diferente?

database-design

2 respostas

  1. Os dados

    Armazenar a mesma combinação de participantes duas vezes é um erro grave de normalização e não deve ser considerado uma opção. Os dados únicos estão em uma das matrizes triangulares correspondentes, por exemplo:

    Brian John Rachel Susana
    Brian Nada Esportes Comida Viagem
    John - Resseguro Filmes Trabalhar
    Rachel - - Auto-depreciativo Música
    Susana - - - Resmungando

    O banco de dados

    O banco de dados deve impor as restrições necessárias.

    • A chave da Conversa é a composta (Participante1, Participante2)
    • Para garantir que uma combinação de participantes seja única, podemos impor uma ordenação entre Participante1 e Participante2, por exemplo, Participante1 <= Participante2.

    insira a descrição da imagem aqui

    CREATE TABLE Person
(
    Person VARCHAR(30),
    CONSTRAINT UC_Person_PK PRIMARY KEY CLUSTERED (Person)
);

CREATE TABLE Conversation
(
    Participant1 VARCHAR(30),
    Participant2 VARCHAR(30),
    Topic        VARCHAR(30),
    CONSTRAINT UC_Conversation_PK PRIMARY KEY CLUSTERED (Participant1, Participant2),
    CONSTRAINT U__Conversation_AK UNIQUE (Participant2, Participant1),
    CONSTRAINT Person_IsParticipant1In_Conversation_fk FOREIGN KEY (Participant1) REFERENCES Person (Person),
    CONSTRAINT Person_IsParticipant2In_Conversation_fk FOREIGN KEY (Participant2) REFERENCES Person (Person),
    CONSTRAINT Participant1_LE_Participant2_ck CHECK (Participant1 <= Participant2)
);

INSERT INTO Person (Person)
VALUES ('Brian'),
       ('John'),
       ('Rachel'),
       ('Susan');

INSERT INTO Conversation (Participant1, Participant2, Topic)
VALUES ('Brian', 'Brian', 'Nothing'),
       ('Brian', 'John', 'Sports'),
       ('Brian', 'Rachel', 'Food'),
       ('Brian', 'Susan', 'Travel'),
       ('John', 'John', 'Reassurance'),
       ('John', 'Rachel', 'Movies'),
       ('John', 'Susan', 'Work'),
       ('Rachel', 'Rachel', 'Self Deprecating'),
       ('Rachel', 'Susan', 'Music'),
       ('Susan', 'Susan', 'Mumbling');

    A pergunta

    Uma simples pergunta "Sobre o que Rachel e Brian conversam" torna-se uma consulta complicada de executar

    Uma consulta ridiculamente complicada é necessária para consultar os dados não redundantes porque você não sabe se Rachel é participante1 ou participante2.

    Supondo que você não possa simplesmente construir a cláusula WHERE com um par ordenado para Participante1 e Participante2, consultar a tabela acima é algo como:

    DECLARE @ParticipantA VARCHAR(30) = 'Rachel';
DECLARE @ParticipantB VARCHAR(30) = 'Brian';
SELECT *
FROM Conversation
WHERE (Participant1 = @ParticipantA AND Participant2 = @ParticipantB)
   OR (Participant1 = @ParticipantB AND Participant2 = @ParticipantA);

    Não vejo como isso pode ficar tão complicado.

    A vista

    Você sempre tem a possibilidade de obter uma visão não normalizada dos dados que contém todas as permutações.

    CREATE VIEW Conversation_V AS
SELECT Participant1, Participant2, Topic
FROM Conversation
UNION
SELECT Participant2 AS Participant1, Participant1 AS Participant2, Topic
FROM Conversation;

DECLARE @ParticipantA VARCHAR(30) = 'Rachel';
DECLARE @ParticipantB VARCHAR(30) = 'Brian';
SELECT *
FROM Conversation_V
WHERE (Participant1 = @ParticipantA AND Participant2 = @ParticipantB);
    • 2
  2. Best Answer

    Um relacionamento "muitos para muitos" normalmente é modelado usando uma tabela de "junção" (ou "link") com chaves estrangeiras apontando para as respectivas entidades.

    No seu caso você terá uma tabela Person , uma tabela Conversation e uma tabela PersonConversations com dois atributos: ConversationIde PersonId.

    Dito isto, a tarefa pode ser alternativamente, e talvez mais naturalmente, modelada por um gráfico e implementada usando um banco de dados de gráficos.

    • 1

relate perguntas