主页 / dba / 问题 / 24817
Accepted
artfulrobot
Asked: 2012-09-25 07:43:26 +0800 CST

接触-接触关系模式的效率

  • 772

我正在将数据从人员和组织是单独表的模式迁移到人员和组织都被视为联系人（他们有很多共同点）的模式。目前，people 表有大约 90k 条记录，与 10k 组织的 80k 关系。

新模式：

Contact           Relationship              (Contact table again)
--------          -------------             ---------------------
cid      11----0< cid_a              /---11 cid
name              cid_b          >0-/       name
                  details
                  start_date
                  end_date
                  relationship_type

如果我想查询Wilma的当前关系（假设 Wilma 有cid = 2），我可以在 上设置 2 个键relationship，一个(cid_a, cid_b)和(cid_b, cid_a)。

SELECT friend.name FROM contact friend, relationship 
WHERE 
     (
       ( cid_a = 2 AND cid_b = friend.cid )
       OR
       ( cid_b = 2 AND cid_a = friend.cid )
     ) 
     AND
     ( start_date IS NULL OR start_date <= CURRENT_DATE )
     AND
     ( end_date IS NULL OR end_date >= CURRENT_DATE )

但我不确定它是否有效，因为重复的键会很长。

联系人可能与各种组织、其他联系人等有 3、4 或更多关系，例如

  • 威尔玛是X大学的学生——
  • 威尔玛是Y组织的成员
  • 威尔玛之前是Z组织的联系人
  • 威尔玛嫁给了弗雷德。

这是独一真道吗？或者什么都不是？！

join optimization

2 个回答

  1. 如果您可以保证每个人只能属于一个组织，那么您就没有理由不添加一个额外的列来将 organization_id 存储到您的表中。然后您可以在查询中使用自联接来获取每个人的组织。

    如果每个人都可以属于多个组织，那么您需要在一个单独的表中维护关系列表，该表具有 person_id 和 organization_id 列，然后将其加入以生成您需要的信息。

    编辑

    根据提供的附加信息，您似乎需要实施第二种方法，其中关系存储在单独的表中。该表应至少包含两列，它们都应是联系人表中主键的外键。这样，一个联系人可以以任何方式与另一个联系人相关联，无论该联系人是组织、个人还是您归类为联系人的任何其他对象。使用此设计，您可以通过从每一列连接联系人表来链接两个键，以获取关系双方的信息。

    我希望这可以帮助你。

    • 4
  2. Best Answer

    既然你调用了 The One True Way... 我会调用它。1NF 会坚持“无重复组”，这就是 cid_a 和 cid_b 是......相同“东西”的两列（使用技术术语）。

    您不必通过两种不同的方式查看数据来获得正确答案。 

    contact           relationship              contact_relationship_map
--------          -------------             ----------------------
cid (PK)          relationship_id (PK)      relationship_id (FK) \\ P   
name              details                   cid (FK)             // K
                  start_date                + INDEX(cid,relationship_id)
                  end_date
                  relationship_type

    每个关系在关系中都有一个记录，它有一个 ID，用于将两行插入到 contact_relationship_map 中——关系中的每个对等点。

    该表的 PK 是两个列的组合，它应该在以相反顺序组合的两个列上建立索引，以便通过 relationship_id 或 cid 进行搜索具有索引的好处。后一个索引不需要声明为唯一的，因为主键会强制执行。两列都不允许父表中的空值和删除级联到该表的记录。

    要查找以“contact”中的名称开头且 relationship_type =“friend”的关系，我们从 c1 开始查找：

    SELECT c2.cid as my_friends_cid, c2.name as my_friends_name 
  FROM contact c1
  JOIN contact_relationship_map crm1 on crm1.cid = c1.cid
  JOIN relationship r on r.id = crm1.relationship_id 
  JOIN contact_relationship_map crm2 on crm2.relationship_id = crm1.relationship_id
                                    and crm2.cid != c1.cid
  JOIN contact c2 on c2.cid = crm2.cid
 WHERE c1.name = 'first_contact_name_here'
   AND r.relationship_type = 'friend';

    换句话说，如下：

    c1 -> crm1 -> crm2 -> c2
          \-> r

    所有这些连接很容易被索引满足，所以这里的连接数量不应该引起任何关注。

    如果您已经知道第一个联系人的 cid，则可以从查询中删除该表，然后从 WHERE crm1.cid = ?

    这也开启了与两个以上同伴建立关系的可能性，如果你愿意的话。

    • 1

相关问题