主页 / dba / 问题 / 335286
Accepted
leigero
Asked: 2024-01-30 00:38:33 +0800 CST

如何为包含多对多关系的 XY 数据网格构建数据模型？

  • 772

数据

假设我有一个网格，代表房间里的所有人以及他们彼此交谈时的对话。

约翰 苏珊 布莱恩 雷切尔
约翰 放心 工作 运动的 电影
苏珊 工作 咕哝着 旅行 音乐
布莱恩 运动的 旅行 没有什么 食物
雷切尔 电影 音乐 食物 自嘲

数据库

最初我考虑只制作一个常规表格People和Conversations

桌子Conversations是一把钥匙，topic participant1 participant2

困境

参与者的顺序完全无关。一个简单的问题“Rachel 和 Brian 谈论什么”变成了一个需要运行的复杂查询。

因此必须做以下两件事之一：

  1. 冗余数据添加到数据库中：（Rachel 和 Brian）或（Brian 和 Rachel）
  2. 查询非冗余数据需要一个极其复杂的查询，因为您不知道 Rachel 是参与者1还是参与者2。

采用上面的 1 会创建一个不可持续的 O(n^2) 数据库模型，而 2 似乎是 DBA 试图查询有意义的数据的噩梦。

问题

如何对这样的数据结构进行建模？SQL 是否是适合这项工作的工具，还是我应该完全研究其他东西？

database-design

2 个回答

  1. 数据

    将相同的参与者组合存储两次是一个严重的标准化错误，不应被视为一种选择。唯一的数据位于相应的三角矩阵之一中，例如：

    布莱恩 约翰 雷切尔 苏珊
    布莱恩 没有什么 运动的 食物 旅行
    约翰 - 放心 电影 工作
    雷切尔 - - 自嘲 音乐
    苏珊 - - - 咕哝着

    数据库

    数据库应该强制执行必要的约束。

    • Conversation的关键是复合（Participant1，Participant2）
    • 为了确保参与者的组合是唯一的，我们可以在Participant1和Participant2之间强制执行排序，例如Participant1 <= Participant2。

    在此输入图像描述

    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');

    查询

    一个简单的问题“Rachel 和 Brian 谈论什么”变成了一个需要运行的复杂查询

    查询非冗余数据需要一个极其复杂的查询，因为您不知道 Rachel 是参与者1还是参与者2。

    假设您不能仅使用 Participant1 和 Participant2 的有序对构造 WHERE 子句，则查询上表类似于：

    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);

    我不明白事情怎么会变得如此复杂。

    风景

    您始终有可能获得包含所有排列的数据的非标准化视图。

    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

    “多对多”关系通常使用具有指向各个实体的外键的“联结”（或“链接”）表来建模。

    在您的例子中，您将有一个Person表、一个Conversation表和一个具有两个属性的PersonConversations表：ConversationId和PersonId。

    话虽如此，该任务也可以通过图来建模，并使用图数据库来实现，这可能更自然。

    • 1

相关问题