簡潔版本

我必須在現有的多對多連接中為每一對添加固定數量的附加屬性。跳到下圖，就優點和缺點而言，選項 1-4 中的哪一個是通過擴展基本案例來實現這一目標的最佳方式？或者，我在這裡沒有考慮過更好的選擇嗎？

更長的版本

我目前通過中間連接表有兩個多對多關係的表。我現在需要向屬於這對現有對象的屬性添加其他連結。我對每一對都有固定數量的這些屬性，儘管屬性表中的一個條目可能適用於多對（甚至可以多次用於一對）。我正在嘗試確定執行此操作的最佳方法，並且無法理清如何考慮這種情況。從語義上看，我似乎可以將其描述為以下任何一種：

1. 一對連結到一組固定數量的附加屬性
2. 一對連結到許多其他屬性
3. 許多（兩個）對象連結到一組屬性
4. 許多對象連結到許多屬性

例子

我有兩個對像類型，X 和 Y，每個都有唯一的 ID，以及一個objx_objy帶有列的連結表x_id和y_id，它們共同構成連結的主鍵。每個 X 可以與多個 Y 相關，反之亦然。這是我現有的多對多關係的設置。

基本情況

現在另外我在另一個表中定義了一組屬性，以及一組給定 (X,Y) 對應該具有屬性 P 的條件。條件的數量是固定的，並且所有對都相同。他們基本上說“在情況 C1 中，對 (X1,Y1) 具有屬性 P1”，“在情況 C2 中，對 (X1,Y1) 具有屬性 P2”等等，對於連接中的每一對的三種情況/條件桌子。

選項1

在我目前的情況下，恰好有三個這樣的條件，我沒有理由期望它會增加，所以一種可能性是添加列c1_p_id,c2_p_id和c3_p_idto featx_featy，指定給定的x_idand ，在這三種情況下使用y_id哪個屬性p_id.

這對我來說似乎不是一個好主意，因為它使 SQL 複雜化以選擇應用於一個特性的所有屬性，並且不容易擴展到更多條件。但是，它確實要求每個 (X,Y) 對具有一定數量的條件。事實上，這是這樣做的唯一選擇。

選項 2

創建條件表cond，並將條件ID添加到連接表的主鍵中。

這樣做的一個缺點是它沒有指定每對條件的數量。另一個是當我只考慮最初的關係時，比如

SELECT objx.*, objy.* FROM objx
 INNER JOIN objx_objy ON objx_objy.x_id = objx.id
 INNER JOIN objy ON objy.id = objx_objy.y_id

然後我必須添加一個DISTINCT子句以避免重複條目。這似乎失去了每一對應該只存在一次的事實。

選項 3

在連接表中創建一個新的“對 ID”，然後在第一個與屬性和條件之間創建第二個連結表。

除了沒有為每對執行固定數量的條件之外，這似乎具有最少的缺點。但是，創建一個僅標識現有 ID 的新 ID 是否有意義？

選項 4 (3b)

與選項 3 基本相同，但沒有創建額外的 ID 欄位。這是通過將兩個原始 ID 放入新連接表中來完成的，因此它包含x_id和y_id欄位，而不是xy_id.

這種形式的另一個優點是它不會改變現有的表（儘管它們還沒有投入生產）。但是，它基本上會多次複製整個表（或者無論如何感覺都是這樣），所以看起來也不理想。

概括

我的感覺是選項 3 和 4 非常相似，我可以選擇其中任何一個。如果不是因為需要少量、固定數量的屬性連結，我現在可能已經擁有了，這使得選項 1 看起來比其他方式更合理。基於一些非常有限的測試，DISTINCT在我的查詢中添加一個子句似乎不會影響這種情況下的性能，但我不確定選項 2 是否代表這種情況以及其他情況，因為放置導致的固有重複連結表的多行中的相同 (X,Y) 對。

這些選項之一是我最好的前進方式，還是我應該考慮另一種結構？

• 選項1

*這對我來說似乎不是一個好主意，因為它使 SQL 複雜化以選擇應用於功能的所有屬性……

它不一定會使查詢 SQL 複雜化（見下面的結論）。

……並且不容易擴展到更多條件……

它很容易擴展到更多的條件，只要仍然有固定數量的條件，而不是幾十個或幾百個。

但是，它確實要求每個 (X,Y) 對具有一定數量的條件。事實上，這是唯一的選擇。*

確實如此，儘管您在評論中說這是“我的要求中最不重要的”，但您並沒有說這根本不重要。

• 選項 2

這樣做的一個缺點是它沒有指定每對條件的數量。另一個是當我只考慮初始關係時……我必須添加一個 DISTINCT 子句以避免重複條目……

由於您提到的複雜性，我認為您可以忽略此選項。該objx_objy表可能是您的某些查詢的驅動表（例如“選擇應用於某個功能的所有屬性”，我將其理解為應用於objxor的所有屬性objy）。您可以使用視圖來預先應用，DISTINCT因此這不是使查詢複雜化的問題，但這會在性能方面非常糟糕地擴展，而收益卻很少。

• 選項 3

但是，創建一個僅標識現有 ID 的新 ID 是否有意義？

不，它沒有——選項 4 在各個方面都更好。

• 選項 4

…它基本上多次複製整個表格（或者感覺那樣，無論如何），所以看起來也不理想。

這個選項很好——如果屬性的數量是可變的或可能發生變化，這是建立關係的明顯方式

結論

objx_objy如果每個屬性的數量可能是穩定的，並且如果您無法想像添加的額外數量超過少數，我的偏好將是選項 1 。它也是強制執行“屬性數 = 3”約束的唯一選項——對選項 4 強制執行類似的約束可能會涉及向c1_p_idxy 表添加……列*。

如果您真的不太在意該條件，並且您也有理由懷疑房產數量條件是否會穩定，請選擇選項 4。

如果您不確定哪個，請選擇選項 1——它更簡單，如果您有這個選項，那肯定會更好，正如其他人所說的那樣。如果您推遲選項 1 “…因為選擇應用於功能的所有屬性會使 SQL 變得複雜…”我建議創建一個視圖以提供與選項 4 中的額外表相同的數據：

選項 1 表：

create table prop(id integer primary key);
create table objx(id integer primary key);
create table objy(id integer primary key);

create table objx_objy(
 x_id integer references objx
, y_id integer references objy
, c1_p_id integer not null references prop
, c2_p_id integer not null references prop
, c3_p_id integer not null references prop
, primary key (x_id, y_id)
);

insert into prop(id) select generate_series(90,99);
insert into objx(id) select generate_series(10,12);
insert into objy(id) select generate_series(20,22);

insert into objx_objy(x_id,y_id,c1_p_id,c2_p_id,c3_p_id)
select objx.id, objy.id, 90, 91, 90+floor(random()*10)
from objx cross join objy;

查看“模擬”選項 4：

create view objx_objy_prop as
select x_id
    , y_id
    , unnest(array[1,2,3]) c_id
    , unnest(array[c1_p_id,c2_p_id,c3_p_id]) p_id
from objx_objy;

“選擇應用於功能的所有屬性”：

select distinct p_id from objx_objy_prop where x_id=10 order by p_id;

/*
|p_id|
|---:|
|  90|
|  91|
|  97|
|  98|
*/

dbfiddle在這裡

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