用大樹處理主鍵，從子表 X 中獲取根 id 的最佳方法

我一直使用這種模式進行數據庫設計；

root table with PK id1 
child1 table with PK id2 FK id1
child2 table with PK id3 FK id2
child3 table with PK id4 FK id3
etc...

所以如果我必須從 child5 表中獲取 id1 我需要遍歷所有子表直到根表（多個連接）

前任;

select root.id
from root
  inner join child1 ...
  inner join child2 ...
  inner join child3 ...
  inner join child4 ...
  inner join child5 ...
where child5.id = X

是“好的”作為設計還是有辦法優化它？

我可以看到一些方法來做到這一點；

1. 將 IDX 作為我期望的表中的 FK，因此我可以根據需要修改（添加正確的 FK）到任何子表
2. 只需在創建新表時添加 FK，這似乎很浪費，例如；child5 表至少有 4 個 FK (id1, id2, id3, id4)

有了這些選項，我可以簡單地做；

select child5.rootid
from child5
where child5.id = X

問題

1. 我上面的任何選項是一件好事還是有更好的方法？
2. 有沒有給這個模式起名字（所以我可以在網上搜尋，我想不出任何名字）

編輯

我有一個例子，根表將是一個報告，在該表中它將有多個欄位，其中包括一年。

結果集必須包含 childX 表中的所有欄位 + 報告的年份。

root表和childX表之間的所有表都有其他欄位但不應該返回，所以它們只用於上root並獲取年份

第二次編輯

CREATE TABLE dbo.root
        (
        root_id int NOT NULL,
        year int NOT NULL,
        field1 int NULL,
        field2 int NULL,
        fieldx int NULL
        )  ON [PRIMARY]

ALTER TABLE dbo.root ADD CONSTRAINT
        PK_root PRIMARY KEY CLUSTERED
        (
        root_id
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

CREATE TABLE dbo.child1
        (
        child1_id int NOT NULL,
        root_id int NOT NULL,
        field1 int NULL,
        field2 int NULL,
        fieldx int NULL
        )  ON [PRIMARY]

ALTER TABLE dbo.child1 ADD CONSTRAINT
        PK_child1 PRIMARY KEY CLUSTERED
        (
        child1_id
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 

ALTER TABLE dbo.child1 ADD CONSTRAINT
        FK_child1_root FOREIGN KEY
        (
        root_id
        ) REFERENCES dbo.root
        (
        root_id
        ) ON UPDATE  NO ACTION
        ON DELETE  NO ACTION
       
CREATE TABLE dbo.child2
        (
        child2_id int NOT NULL,
        child1_id int NOT NULL,
        field1 int NULL,
        field2 int NULL,
        fieldx int NULL
        )  ON [PRIMARY]

ALTER TABLE dbo.child2 ADD CONSTRAINT
        PK_child2 PRIMARY KEY CLUSTERED
        (
        child2_id
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 

ALTER TABLE dbo.child2 ADD CONSTRAINT
        FK_child2_child1 FOREIGN KEY
        (
        child1_id
        ) REFERENCES dbo.child1
        (
        child1_id
        ) ON UPDATE  NO ACTION
        ON DELETE  NO ACTION
       
CREATE TABLE dbo.child3
        (
        child3_id int NOT NULL,
        child2_id int NOT NULL,
        field1 int NULL,
        field2 int NULL,
        fieldx int NULL
        )  ON [PRIMARY]

ALTER TABLE dbo.child3 ADD CONSTRAINT
        PK_child3 PRIMARY KEY CLUSTERED
        (
        child3_id
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 

ALTER TABLE dbo.child3 ADD CONSTRAINT
        FK_child3_child2 FOREIGN KEY
        (
        child2_id
        ) REFERENCES dbo.child2
        (
        child2_id
        ) ON UPDATE  NO ACTION
        ON DELETE  NO ACTION
       
CREATE TABLE dbo.child4
        (
        child4_id int NOT NULL,
        child3_id int NOT NULL,
        field1 int NULL,
        field2 int NULL,
        fieldx int NULL
        )  ON [PRIMARY]

ALTER TABLE dbo.child4 ADD CONSTRAINT
        PK_child4 PRIMARY KEY CLUSTERED
        (
        child4_id
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 

ALTER TABLE dbo.child4 ADD CONSTRAINT
        FK_child4_child3 FOREIGN KEY
        (
        child3_id
        ) REFERENCES dbo.child3
        (
        child3_id
        ) ON UPDATE  NO ACTION
        ON DELETE  NO ACTION
       
CREATE TABLE dbo.child5
        (
        child5_id int NOT NULL,
        child4_id int NOT NULL,
        field1 int NULL,
        field2 int NULL,
        fieldx int NULL
        )  ON [PRIMARY]

ALTER TABLE dbo.child5 ADD CONSTRAINT
        PK_child5 PRIMARY KEY CLUSTERED
        (
        child5_id
        ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
 

ALTER TABLE dbo.child5 ADD CONSTRAINT
        FK_child5_child4 FOREIGN KEY
        (
        child4_id
        ) REFERENCES dbo.child4
        (
        child4_id
        ) ON UPDATE  NO ACTION
        ON DELETE  NO ACTION

insert into root select 1,2021, null, null, null  
insert into child1 select 1,1, null, null, null  
insert into child2 select 1,1, null, null, null  
insert into child3 select 1,1, null, null, null  
insert into child4 select 1,1, null, null, null
insert into child5 select 1,1, 10, 20, 30  
insert into child5 select 2,1, 100, 200, 300

select r.year, c5.field1, c5.field2, c5.fieldx
from root r
inner join child1 c1 on r.root_id = c1.root_id
inner join child2 c2 on c1.child1_id = c2.child1_id
inner join child3 c3 on c2.child2_id = c3.child2_id
inner join child4 c4 on c3.child3_id = c4.child3_id
inner join child5 c5 on c4.child4_id = c5.child4_id
where c5.child5_id = 2

GO

年份 | 欄位1 | 欄位2 | 欄位x
---: | -----: | -----: | -----:
2021 | 100 | 200 | 300

db<>在這裡擺弄

從一個非常基本的概述來看，我認為您現有的設計並不壞。一般來說，最好從表格設計中遵循某種*規範化的模式開始。*通常，我會盡量保持我的表足夠薄以限定它們所代表的基本對象，通常將高度相關的欄位分組到同一個表中。如果描述該實體的使用較少、重要性較低或可選欄位較多，那麼我通常會將它們重構為一個EntityExtended排序表。

在您的情況下，聽起來您有多個表，每個表都有唯一的引用約束，這很正常。在不知道表格和/或上下文的實際內容的情況下，很難建議它們是否可以更好地重組。但總的來說，從性能的角度來看，您的設計不應該有任何問題，除非您總是從root表查詢到child5. 然後遵循更非規範化類型的模式可能比總是重新創建相同的多個連接更有意義。

將行大小（以最小化數據頁的數量）減少到重新創建最常查詢的數據集通常需要的連接數的性能總是在兩個方向上進行權衡。因此，這將取決於您的案例和最常見的查詢，但您可以對此進行測試，看看哪種方法最適合您的需求。如果您希望使用更具體的細節來限定您的文章以將您的架構和數據類型上下文化，我們也許能夠提供更具體的建議。

我唯一可以提到的另一件事是無論性能如何，您可能會發現創建視圖以提高數據庫的可用性和結構，因此您不必重複連接很有用並且可以減少您的程式碼庫。視圖通常不會以某種方式影響性能，但從可維護性的角度來看會有所幫助。

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