聯繫聯繫模式的效率

我正在將數據從人員和組織是單獨的表的模式遷移到人員和組織都被視為聯繫人的模式（它們有很多共同點）。目前，人員表有大約 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 個或更多關係，例如

• Wilma是X大學的學生-
• 威爾瑪是Y組織的成員
• Wilma 之前是 Z 組織的聯繫人
• 威爾瑪嫁給了弗雷德。

這是一真道嗎？還是什麼都不像？！

既然你呼叫了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’ 中的名稱和=‘friend’ 的relationship_type 開頭的關係，我們從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 = ?

如果您願意的話，這也開闢了與兩個以上同伴建立關係的可能性。

引用自：https://dba.stackexchange.com/questions/24817