建模具有多個一對多父母的關係

我試圖模擬一種可能有兩個父母的關係，其中一個是可選的，另一個是強制性的。設計應具有以下規則，

• AHouse 必須作為頂級父級存在
• 作為父母Bedroom 必須有房子
• AHouse可以有很多Bedrooms
• 一個House可以有很多Photos的房子
• APhoto也可以選擇連結到 a Bedroom，即它是房子中臥室的照片
• AUser應該只能訪問 a和他們擁有的House任何子（Bedroom和資源）（通過表中的欄位）Photo``UserID``House

我目前的設計是這樣的：

   +---------+         +-----------+
   | House   |         | Bedroom   |
   +---------+         +-----------+
+---> HouseID <---+     | BedroomID <----+
|   | UserID  |   +-----+ HouseID   |    |
|   | ...     |         | ...       |    |
|   +---------+         +-----------+    |
|                                        |
|                                        |
|           +-----------+                |
|           | Photo     |                |
|           +-----------+                |
|           | PhotoID   |                |
+-----------+ HouseID   |   OPTIONAL     |
           | BedroomID +----------------+
           | ...       |
           +-----------+

我目前強制 aPhoto 必須House通過 FK連結到 a ，所以我可以執行 aJOIN來獲取原始UserID，因為我知道這個鍵已經存在，但感覺這不是正確的方法。

理想情況下，我希望返回僅在查詢有關 的數據時連結到 的Photos ，然後在查詢有關 a 的數據時僅連結到a 。我知道通過 s 很容易做到這一點，但這讓我懷疑這是否是正確的設計，因為它似乎更像是“圍繞設計工作”而不是解決根本問題。House``House``Photo``Bedroom``Bedroom``JOIN

在這裡使用映射表會更有用嗎？或者關於如何改進它的任何進一步的想法？

我認為你的桌子設計很好而且規範化了。

還不能保證的一件事是，如果 aphoto引用 a bedroom，那bedroom應該house與photo.

你可以通過一個額外的UNIQUE約束來獲得它：

ALTER TABLE bedroom
  ADD UNIQUE (houseid, bedroomid);

那麼外鍵約束可以定義為：

ALTER TABLE photo
  ADD FOREIGN KEY (houseid, bedroomid) REFERENCES bedroom (houseid, bedroomid);

從性能角度來看，額外的索引並不好，但是由於外鍵，您無論如何都希望在houseid列上建立索引bedroom，因此額外的列不應該受到傷害。

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