我正在使用 SQL Server Express 2017 創建一個小型數據庫。

• 有兩種動物（狐狸和大象）。
• Foxes 表將包含以下列：Identifier、 Species、BodyLength和FurColour。
• Elephant 表將具有：Identifier、Species、 ElephantWeightKG和TuskLengthCM。
• 每隻動物都有一個“項目”列表，我有一張Items桌子，狐狸和大像都共享。
• 目前，Fox 和 Elephant 標識符之間沒有重疊。但是將來標識符的格式可能會發生變化（最壞的情況可能會有一些重疊）。數據庫應該被設計來處理這個問題。

經過一些反複試驗和研究，這就是我想出的：

create table Foxes
(
   Identifier varchar(50) not null constraint PK_FoxIdentifier primary key,
   Species varchar(50),
   BodyLength int,
   FurColour varchar(50),
);

create table Elephants
(
   Identifier varchar(50) not null constraint PK_ElephantsIdentifier primary key,
   Species varchar(50),
   ElephantWeightKG int,
   TuskLengthCM int,
);

create table Items
(
   ItemSeq int identity(1,1),
   ItemID as isnull(FoxID, ElephantID) + '-' + cast(ItemSeq as varchar) persisted primary key,
   FoxID varchar(50),
   ElephantID varchar(50),
   ItemDescription varchar(500),

   foreign key (FoxID) references Foxes(Identifier) on delete cascade on update cascade,
   foreign key (ElephantID) references Elephants(Identifier) on delete cascade on update cascade,

   constraint CK_FoxIDElephantsItemIdentifier check
   (
       case when FoxID is null then 0 else 1 end +
       case when ElephantID is null then 0 else 1 end = 1
   )
);

這是一個可以接受的設計嗎？你將做點什麼不同的？

謝謝。

從純概念的角度分析場景，迄今為止被錯誤表示的一種重要實體類型是Animal，這將有助於 (a) 更準確地傳達所有相關關聯，並 (b) 在邏輯上更清晰地描繪整個數據庫等級。

這樣：

• Fox和Elephant將成為Animal的實體子類型，而後者又將成為它們的實體超類型。
• 由於每個特定的Animal實例要麼是Fox 要麼是Elephant - 從不兩者兼有 - 這意味著子類型實例是互斥的。
• 任何給定的Animal事件——無論是Fox還是Elephant——都可能連結到零個、一個或多個Item事件，而Item實例將連結到一個Animal對應物——無論是Fox還是Elephant——。

---

注意：在一些概念建模方法中，超類型-子類型關聯被稱為超類/子類關係。

---

說明性邏輯 DDL 設計

因此，我將按照以下方式設置相應的邏輯級設計：

-- You should determine which are the most fitting 
-- data types and sizes for all your table columns depending
-- on the applicable business context characteristics.

-- Also, you should make accurate tests to define the most
-- convenient physical-level index strategies.

-- As one would expect, you are free to make use of 
-- your preferred (or required) naming conventions. 

CREATE TABLE Species ( 
   SpeciesCode CHAR(1)  NOT NULL, 
   Name        CHAR(30) NOT NULL, 
   --  
   CONSTRAINT Species_PK PRIMARY KEY (SpeciesCode),
   CONSTRAINT Species_AK UNIQUE      (Name)        
);

-- “Populating” the Species table:
INSERT INTO Species
   (SpeciesCode, Name)
VALUES
   ('F', 'Fox'),
   ('E', 'Elephant');

CREATE TABLE Animal ( -- Represents the supertype.
   AnimalId        INT       NOT NULL IDENTITY (1,1), -- Column meant to enclosed the Identifiers as established in the business context of relevance.
   SpeciesCode     CHAR(1)   NOT NULL, --Stands for the discriminator property.
   CreatedDateTime DATETIME  NOT NULL,  
   --
   CONSTRAINT Animal_PK            PRIMARY KEY (AnimalId),
   CONSTRAINT Animal_to_Species_FK FOREIGN KEY (SpeciesCode)
       REFERENCES Species (SpeciesCode)
);

CREATE TABLE Fox ( -- Conveys one of the subtypes.
   FoxId      INT         NOT NULL, -- No need for system-controlled surrogate values (e.g. those generated with the IDENTITY property) in this column.
   BusinessId VARCHAR(50) NOT NULL, -- This column is supposed to retain the Identifiers as established in the business context of relevance.
   BodyLength INT         NOT NULL,
   FurColour  VARCHAR     NOT NULL,
   --
   CONSTRAINT Fox_PK           PRIMARY KEY (FoxId),
   CONSTRAINT Fox_AK           UNIQUE      (BusinessId), 
   CONSTRAINT Fox_to_Animal_FK FOREIGN KEY (FoxId)
       REFERENCES Animal (AnimalId)
);

CREATE TABLE Elephant ( -- Represents the other subtype.
   ElephantId INT         NOT NULL, -- No need for system-controlled surrogate values (e.g. those generated with the IDENTITY property) in this column either.
   BusinessId VARCHAR(50) NOT NULL, -- This column is supposed to retain the Identifiers as established in the business context of relevance.
   Weight     INT         NOT NULL,
   TuskLength INT         NOT NULL, 
   --
   CONSTRAINT Elephant_PK           PRIMARY KEY (ElephantId),
   CONSTRAINT Elephant_AK           UNIQUE      (BusinessId), 
   CONSTRAINT Elephant_to_Animal_FK FOREIGN KEY (ElephantId)
       REFERENCES Animal (AnimalId)
);

CREATE TABLE Item ( 
   ItemSeq         INT          NOT NULL IDENTITY (1,1), 
   AnimalId        INT          NOT NULL,
   Description     VARCHAR(500) NOT NULL, 
   CreatedDateTime DATETIME     NOT NULL,  
   --
   CONSTRAINT Item_PK           PRIMARY KEY (ItemSeq),
   CONSTRAINT Item_to_Animal_FK FOREIGN KEY (AnimalId)
       REFERENCES Animal (AnimalId)        
);

---

這個 db<>fiddle包含上面顯示的 DDL 佈局，以便您可以看到它的執行情況。

---

列FoxId和ElephantId被約束為適當表的 PK，有助於通過指向列的 FK 約束來表示概念級別的一對一關聯，該AnimalId列被約束為Animal表的 PK。這意味著，在實際的“對”中，超類型行和子類型行都由相同的 PK 值標識；因此，值得一提的是（a）附加一個額外的列來保存系統控制的代理值到（b）代表子類型的表是（c）完全多餘的。

如您所見，我添加了 ，Animal.CreatedDateTime因為我認為知道將特定Animal行插入數據庫的確切時間點很重要。

以這種方式，通過相應的行和合適的約束“間接地”建立了Item行和Fox或行之間的關聯。Elephant``Animal

該Item.AnimalId列防止 (1) 一列用於 TheItems.FoxId和 (2) 一列用於 The Items.ElephantId，它們所涉及的所有歧義和不必要的複雜性（例如，接受 NULL 標記以及它們對數據操作的影響，添加一個 CHECK 約束，如您添加到Items表中的一個，等等）。

FurColour數據的“查找”表

我不熟悉狐狸的毛皮顏色（例如，我不知道狐狸標本呈現了多少毛皮顏色，或者**毛皮顏色是如何組成的，等等），但您可能想評估是否包含一個提供“外觀”的表格up” 角色的此類資訊，而這些資訊又可以從表中的 FOREIGN KEY 約束中引用。Fox

完整性和一致性考慮

最重要的是，在您的業務環境中，您必須 (1) 確保每個“超類型”行始終由其對應的“子類型”對應物補充，並且反過來，(2) 保證說“ subtype”行與“supertype”行的“discriminator”列中包含的值兼容。

借助斷言以聲明性方式強制執行此類情況會非常優雅，但不幸的是，沒有一個主要的 SQL 平台對這些強大的工具（它們是此類工作的合適工具）提供適當的支持。因此，在ACID TRANSACTIONs中使用過程程式碼非常方便，因此這些條件在您的數據庫中始終得到滿足。其他選擇是使用 TRIGGER（也是程序性的），但它們往往會使事情變得不整潔。

創建視圖以修復有用的派生表

似乎列FoxID和表的目的ElephantID之一Items是它們有助於確定Item行連結到的**Animal行的類型，但是通過視圖（即派生表），範例如下：

CREATE VIEW ItemWithAnimal AS
   SELECT I.ItemSeq,
          A.AnimalId,
          A.SpeciesCode,
          I.Description,
          I.CreatedDateTime AS ItemCreatedDateTime,
          A.CreatedDateTime AS AnimalCreatedDateTime
       FROM Item   I
       JOIN Animal A
         ON I.AnimalId = A.AnimalId;

…SpeciesCode列值表示AnimalId關注值的確切種類，因此解釋數據的人可以查看所考慮的項目是否與Fox或Animal相關聯。所以當你想檢索Item資訊時，你可以直接從這個派生表或視圖中選擇，而不是從Item基表中選擇。

擁有獲取“完整” Fox或Elephant資訊的視圖似乎也很有用，例如：

CREATE VIEW FullFox AS
   SELECT A.AnimalId,
          F.BusinessId,
          F.BodyLength,
          F.FurColor,
          A.CreatedDateTime
       FROM Fox    F
       JOIN Animal A
         ON F.FoxId = A.AnimalId;

Animal顯然，如果表包含更多旨在由Foxor表“共享”的列，則此視圖會更有益Elephant，但值得說明視圖的好處。

---

上面討論的視圖也包含在先前連結到的 db<>fiddle中。

---

查看您的Items.ItemID,Items.FoxID和Items.ElephantID列

您的列似乎Items.ItemID是某種可派生列，即它包含的值是根據表的其他兩個列中包含的值計算的，即，Items.FoxID 或 Items.ElephanID 連同 Items.ItemSeq（這是不必要的）。我強烈建議您避免對任何類型的列採取這種行動，但由於Items.ItemID受到主鍵（PK）的限制，我更加強調我的建議。

這樣，如果在您的業務領域中，一個Item通過其ItemSeq和相關動物的標識符*（無論是Fox還是Elephant ）的組合來唯一**區分*，那麼您可以調整我為表格闡述的邏輯安排通過聲明兩列複合主鍵並避免使用可派生 PK 的工作。Item``(AnimalId, ItemSeq)

其他涉及超類型-子類型關聯的場景

如果您想查看更多超類型-子類型關係的範例，它們是在性質非常不同的業務環境中出現的典型資料結構，您可能會感興趣，例如，我對以下問題的回答：

• “具有兩種可能的所有者/父類型的實體的數據庫模式？”
• “為資金轉移業務開發數據庫， (a) 個人和組織可以 (b) 發送和接收資金”
• “如何對可以具有不同屬性集的實體類型進行建模？”

您可能還想查看按子類型標籤分組的其餘文章。

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