Foreign-Key
是否建議有多餘的外鍵列?
想像一下,我有模型
A
,B
,C
eD
,其中A
有很多B
s、B
很多C
s 和C
很多D
s:沒有冗餘
A | id | ... | ------------ | ... | ... | B | id | a_id | ... | -------------------- | ... | ... | ... | C | id | b_id | ... | -------------------- | ... | ... | ... | D | id | c_id | ... | -------------------- | ... | ... | ... |
建議在
C
e中包含更多列D
並參考A
andB
?有冗餘
C | id | a_id | b_id | ... | --------------------------- | ... | ... | ... | ... | D | id | a_id | b_id | c_id | ... | ---------------------------------- | ... | ... | ... | ... | ... |
這是一個冗餘,但我通常這樣做是為了進行更簡單的查詢,我可以減少
JOIN
s. 我認為它可能也有更好的性能。推薦嗎?(至少當列是不可變的時)有沒有更好的解決方案?
將列添加到滿足系統要求的表中,我找不到任何錯誤。
實際上,存在“冗餘”列可能會使某些查詢更快。舉這個非常人為的例子,你想在其中獲取父母的名單,以及他們孩子就讀的學校的名稱。第一個例子,父母與學校沒有直接聯繫:
SELECT p.ParentName , s.SchoolName FROM dbo.Schools s INNER JOIN dbo.Children c on s.School_ID = c.School_ID INNER JOIN dbo.Parents p ON c.Parent_ID = p.Parent_ID;
與家長連結到學校的查詢相比,即使連結是“冗餘的”,因為每個家長的孩子都已經有了連結:
SELECT p.ParentName , s.SchoolName FROM dbo.Schools s INNER JOIN dbo.Parents ON s.School_ID = p.School_ID
顯然,您希望執行第二個變體,因為 (a) 理解意圖不那麼複雜,並且 (b) SQL Server 必須做的工作更少,這總是一件好事。
https://stackoverflow.com/questions/40553231/storing-redundant-foreign-keys-to-avoid-joins
對於@max-vernon 的回答,第一個sql 確實比第二個多,但不會太多。我更喜歡第一個。所以:
// you have D, and want to get A, then: let d = D(); let c = select C.* from C where C.id=d.c_id; let b = select B.* from B where B.id=c.b_id; let a = select A.* from A where A.id=b.a_id; // or let d = D(); let a = select A.* from A right join B on A.id=B.a_id right join C on B.id=C.b_id right join D on C.id=D.c_id where D.id=d.id; // you have A, and want to get Ds, then: let a = A(); let Bs = select B.* from B where B.a_id=a.id; let Cs = select C.* from C where C.b_id in Bs.map(b => b.id); let Ds = select D.* from D where D.c_id in Cs.map(c => c.id); // or let a = A(); let Ds = select D.* from D left join C on C.id=D.c_id left join B on B.id=C.b_id left join A on A.id=B.a_id where A.id=a.id;