我在重新創建具有Users和Items之間的一對多（1：M）關係的數據庫時遇到了一些麻煩。

這很簡單，是的；但是，每個項目都屬於某個類別（例如，汽車、船或飛機），並且每個類別都有特定數量的屬性，例如：

Car結構體：

+----+--------------+--------------+
| PK | Attribute #1 | Attribute #2 |
+----+--------------+--------------+

Boat結構體：

+----+--------------+--------------+--------------+
| PK | Attribute #1 | Attribute #2 | Attribute #3 |
+----+--------------+--------------+--------------+

Plane結構體：

+----+--------------+--------------+--------------+--------------+
| PK | Attribute #1 | Attribute #2 | Attribute #3 | Attribute #4 |
+----+--------------+--------------+--------------+--------------+

由於屬性（列）數量的多樣性，我最初認為為每個Category創建一個單獨的表是個好主意，所以我會避免幾個NULL，從而更好地利用索引。

雖然一開始看起來很棒，但我找不到通過數據庫創建項目和類別之間關係的方法，因為至少在我作為數據庫管理員的經驗中，在創建外鍵時，我明確地通知數據庫表名和列。

最後，我想要一個可靠的結構來儲存所有數據，同時擁有所有方法來列出使用者在一次查詢中可能擁有的所有項目的所有屬性。

我可以用伺服器端語言對動態查詢進行**硬編碼，但我覺得這是錯誤的，而且不是很理想。

附加資訊

這些是我對 MDCCL 評論的回應：

**1.**在您的業務環境中有多少個感興趣的項目類別，三個（即汽車、船和飛機）或更多？

其實很簡單：一共只有五個類別。

**2.**同一個項目是否總是屬於同一個使用者（即，一旦給定項目“分配”給某個使用者，它就不能更改）？

不，他們可以改變。在問題的虛構場景中，就像使用者 A 為使用者 B 出售 Item #1一樣，因此必須反映所有權。

**3.**是否有一些或所有類別共享的屬性？

不共享，但從記憶中，我可以看出所有類別中至少存在三個屬性。

4. User和Item之間關係的基數是否有可能是多對多 (M:N) 而不是一對多 (1:M)？例如，在以下業務規則的情況下：A User owns zero-one-or-many Items和An Item is owned by one-to-many Users

不，因為項目將描述一個物理對象。使用者將擁有它們的虛擬副本，每個都由唯一的GUID v4標識

5、關於您對其中一個問題評論的以下回复：

> > “在問題的虛構場景中，就像使用者 A 為使用者 B 出售 Item #1一樣，因此必須反映所有權。” > > >

可以這麼說，您似乎正計劃跟踪項目所有權的演變。這樣，對於這種現象，您希望儲存哪些屬性？僅修改指示特定使用者是特定項目**所有者的屬性？

不，不是。所有權可能會改變，但我不需要跟踪以前的Owner。

根據您對所考慮的業務環境的描述，存在一個超類型-子類型結構，其中包含Item — 超類型 — 及其每個 Category ，即Car、Boat和Plane（以及另外兩個未公開的）-亞型——。

我將在下面詳細介紹我將用來管理所述場景的方法。

商業規則

為了開始描繪相關的概念架構，目前確定的一些最重要的業務規則（將分析僅限於三個披露的類別，以使事情盡可能簡短）可以製定如下：

• 使用者擁有零個一個或多個**Items。

• 一個項目在特定時刻由一個使用者擁有。

• 一個項目可能在不同的時間點由一對多使用者擁有。

• 一個Item由一個Category 分類。

• 一個項目，在任何時候，

  • 要麼是汽車
  • 或一艘船
  • 或飛機。

說明性 IDEF1X 圖

圖 1顯示了一個 IDEF1X 1圖表，我創建該圖表以將之前的公式與其他相關的業務規則一起分組：

超類型

一方面，超類型Item表示所有**Categories共有的屬性**†**或屬性，即

• CategoryCode — 指定為引用Category.CategoryCode並用作子類型鑑別器的外鍵 (FK) ，即，它指示給定項目必須與之連接的子類型的確切類別—，
• OwnerId — 區別為指向User.UserId的 FK ，但我為它分配了一個角色名稱2以便更準確地反映其特殊含義—，
• 富,
• 酒吧，
• 巴茲和
• 創建日期時間。

亞型

另一方面，屬於每個特定類別的屬性**‡**，即

• Qux和Corge ;
• Grault、Garply和Plugh；
• Xyzzy、Thud、Wibble和Flob；

顯示在相應的子類型框中。

身份標識

那麼，Item.ItemId PRIMARY KEY（PK）已經遷移了3到不同角色名的子類型，即

• 車牌號，
• 船名和
• 計劃編號。

互斥關聯

如圖所示， (a) 每個超類型出現和 (b) 其互補子類型實例之間存在一對一(1:1) 基數比的關聯或關係。

獨占子類型符號描繪了子類型互斥的事實，即，一個具體的Item出現只能由一個子類型實例補充：一個Car或一個Plane或一個Boat（從不為零或更少，也不由兩個或更多）。

† , **‡**我使用經典的佔位符名稱來賦予某些實體類型屬性的權利，因為問題中沒有提供它們的實際面額。

說明性邏輯級佈局

因此，為了討論說明性邏輯設計，我根據上面顯示和描述的 IDEF1X 圖派生了以下 SQL-DDL 語句：

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

-- Also, you should make accurate tests to define the 
-- most convenient INDEX strategies based on the exact 
-- data manipulation tendencies of your business context.

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

CREATE TABLE UserProfile (
   UserId          INT      NOT NULL,
   FirstName       CHAR(30) NOT NULL,
   LastName        CHAR(30) NOT NULL,
   BirthDate       DATE     NOT NULL,
   GenderCode      CHAR(3)  NOT NULL,
   Username        CHAR(20) NOT NULL,
   CreatedDateTime DATETIME NOT NULL,
   --
   CONSTRAINT UserProfile_PK  PRIMARY KEY (UserId),
   CONSTRAINT UserProfile_AK1 UNIQUE ( -- Composite ALTERNATE KEY.
       FirstName,
       LastName,
       GenderCode,
       BirthDate
   ),
   CONSTRAINT UserProfile_AK2 UNIQUE (Username) -- ALTERNATE KEY.
);

CREATE TABLE Category (
   CategoryCode     CHAR(1)  NOT NULL, -- Meant to contain meaningful, short and stable values, e.g.; 'C' for 'Car'; 'B' for 'Boat'; 'P' for 'Plane'.
   Name             CHAR(30) NOT NULL,
   --
   CONSTRAINT Category_PK PRIMARY KEY (CategoryCode),
   CONSTRAINT Category_AK UNIQUE      (Name) -- ALTERNATE KEY.
);

CREATE TABLE Item ( -- Stands for the supertype.
   ItemId           INT      NOT NULL,
   OwnerId          INT      NOT NULL,
   CategoryCode     CHAR(1)  NOT NULL, -- Denotes the subtype discriminator.
   Foo              CHAR(30) NOT NULL,
   Bar              CHAR(40) NOT NULL,
   Baz              CHAR(55) NOT NULL,  
   CreatedDateTime  DATETIME NOT NULL,
   --
   CONSTRAINT Item_PK             PRIMARY KEY (ItemId),
   CONSTRAINT Item_to_Category_FK FOREIGN KEY (CategoryCode)
       REFERENCES Category    (CategoryCode),
   CONSTRAINT Item_to_User_FK     FOREIGN KEY (OwnerId)
       REFERENCES UserProfile (UserId)  
);

CREATE TABLE Car ( -- Represents one of the subtypes.
   CarId INT          NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
   Qux   DATE         NOT NULL,
   Corge DECIMAL(5,2) NOT NULL,   
   --
   CONSTRAINT Car_PK         PRIMARY KEY (CarId),
   CONSTRAINT Car_to_Item_FK FOREIGN KEY (CarId)
       REFERENCES Item (ItemId),
   CONSTRAINT ValidQux_CK    CHECK       (Qux >= '1990-01-01')   
);

CREATE TABLE Boat ( -- Stands for one of the subtypes.
   BoatId INT      NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
   Grault SMALLINT NOT NULL,
   Garply DATETIME NOT NULL,   
   Plugh  CHAR(63) NOT NULL, 
   --
   CONSTRAINT Boat_PK         PRIMARY KEY (BoatId),
   CONSTRAINT Boat_to_Item_FK FOREIGN KEY (BoatId)
       REFERENCES Item (ItemId),
   CONSTRAINT ValidGrault_CK  CHECK       (Grault <= 10000)  
);

CREATE TABLE Plane ( -- Denotes one of the subtypes.
   PlaneId INT      NOT NULL, -- Must be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
   Xyzzy   BIGINT   NOT NULL,
   Thud    TEXT     NOT NULL,  
   Wibble  CHAR(20) NOT NULL, 
   Flob    BIT(1)   NOT NULL,   
   --
   CONSTRAINT Plane_PK         PRIMARY KEY (PlaneId),
   CONSTRAINT Plane_to_Item_PK FOREIGN KEY (PlaneId)
       REFERENCES Item (ItemId),
   CONSTRAINT ValidXyzzy_CK    CHECK       (Xyzzy <= 3258594758)
);

這已經在 MySQL 8.0 上執行的這個 db<>fiddle中進行了測試。

如圖所示，超實體類型和每個子實體類型由相應的基表表示。

列CarId和被約束為適當表的 PK BoatId，有助於通過指向列的 FK 約束§PlaneId表示概念級別的一對一關聯，該列被約束為表的 PK。這意味著，在實際的“對”中，超類型行和子類型行都由相同的 PK 值標識；因此，值得一提的是ItemId``Item

• （a）附加一個額外的列來保存系統控制的代理值‖到（b）代表子類型的表是（c）完全多餘的。

§為了防止與（特別是 FOREIGN）KEY 約束定義有關的問題和錯誤——您在評論中提到的情況——考慮到手頭不同表之間發生的存在依賴性非常重要，如說明性 DDL 結構中表的聲明順序，我也在這個 db<>fiddle中提供。

‖例如，將具有AUTO_INCREMENT屬性的附加列附加到基於 MySQL 的數據庫的表中。

完整性和一致性考慮

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

以聲明的方式強制執行這種情況會非常優雅，但不幸的是，據我所知，沒有一個主要的 SQL 平台提供適當的機制來執行此操作。因此，使用ACID TRANSACTIONS中的程式碼非常方便，因此您的數據庫中始終滿足這些條件。其他選擇是使用觸發器，但可以這麼說，它們往往會使事情變得不整潔。

聲明有用的視圖

具有與上述類似的邏輯設計，創建一個或多個視圖，即包含屬於兩個或多個相關基表的列的**派生表將是非常實用的。通過這種方式，您可以，例如，直接從這些視圖中選擇，而不必在每次必須檢索“組合”資訊時編寫所有 JOIN。

樣本數據

在這方面，假設基表“填充”瞭如下所示的範例數據：

--
INSERT INTO UserProfile 
   (UserId, FirstName, LastName, BirthDate, GenderCode, Username, CreatedDateTime)
VALUES
   (1, 'Edgar', 'Codd', '1923-08-19', 'M', 'ted.codd', CURDATE()),
   (2, 'Michelangelo', 'Buonarroti', '1475-03-06', 'M', 'michelangelo', CURDATE()),
   (3, 'Diego', 'Velázquez', '1599-06-06', 'M', 'd.velazquez', CURDATE());

INSERT INTO Category 
   (CategoryCode, Name)
VALUES
   ('C', 'Car'), ('B', 'Boat'), ('P', 'Plane');

-- 1. ‘Full’ Car INSERTion

-- 1.1 
INSERT INTO Item
   (ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
   (1, 1, 'C', 'Motorway', 'Tire', 'Chauffeur', CURDATE());

-- 1.2
INSERT INTO Car
   (CarId, Qux, Corge)
VALUES
   (1, '1999-06-11',  999.99);

-- 2. ‘Full’ Boat INSERTion

-- 2.1
INSERT INTO Item
   (ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
   (2, 2, 'B', 'Ocean', 'Anchor', 'Sailor', CURDATE());

-- 2.2
INSERT INTO Boat
   (BoatId, Grault, Garply, Plugh)
VALUES
   (2, 10000, '2016-03-09 07:32:04.000', 'So far so good.');

-- 3 ‘Full’ Plane INSERTion

-- 3.1   
INSERT INTO Item
   (ItemId, OwnerId, CategoryCode, Foo, Bar, Baz, CreatedDateTime)
VALUES
   (3, 3, 'P', 'Sky', 'Wing', 'Aviator', CURDATE());

-- 3.2

INSERT INTO Plane
   (PlaneId, Xyzzy, Thud, Wibble, Flob)
VALUES
   (3, 3258594758, 'Lorem ipsum dolor sit amet, consectetur adipiscing elit. Ut sollicitudin pharetra sem id elementum. Sed tempor hendrerit orci. Ut scelerisque pretium diam, eu sodales ante sagittis ut. Phasellus id nunc commodo, sagittis urna vitae, auctor ex. Duis elit tellus, pharetra sed ipsum sit amet, bibendum dapibus mauris. Morbi condimentum laoreet justo, quis auctor leo rutrum eu. Sed id nibh non leo sodales pulvinar. Nam ornare ipsum nunc, eget molestie nulla ultrices vel. Curabitur fermentum nisl quis lorem aliquam pretium aliquam at mauris. In vestibulum, tellus et pharetra sollicitudin, mi lacus consectetur dolor, id volutpat nulla eros a mauris. ', 'Here we go!', TRUE);

--

然後，一個有利的視圖是從 和 收集列Item的Car視圖UserProfile：

--

CREATE VIEW CarAndOwner AS
   SELECT C.CarId,
          I.Foo,
          I.Bar,
          I.Baz,
          C.Qux,
          C.Corge,           
          U.FirstName AS OwnerFirstName,
          U.LastName  AS OwnerLastName
       FROM Item I
       JOIN Car C
         ON C.CarId = I.ItemId
       JOIN UserProfile U
         ON U.UserId = I.OwnerId;

--

自然地，可以遵循類似的方法，以便您也可以直接從一個表（在這些情況下是派生表）中選擇“完整”Boat和資訊。Plane

之後——如果你不介意結果集中存在 NULL 標記——使用下面的 VIEW 定義，你可以例如從表Item、Car、Boat和Plane中“收集”列UserProfile：

--

CREATE VIEW FullItemAndOwner AS
   SELECT I.ItemId,
          I.Foo, -- Common to all Categories.
          I.Bar, -- Common to all Categories.
          I.Baz, -- Common to all Categories.
         IC.Name      AS Category,
          C.Qux,    -- Applies to Cars only.
          C.Corge,  -- Applies to Cars only.
          --
          B.Grault, -- Applies to Boats only.
          B.Garply, -- Applies to Boats only.
          B.Plugh,  -- Applies to Boats only.
          --
          P.Xyzzy,  -- Applies to Planes only.
          P.Thud,   -- Applies to Planes only.
          P.Wibble, -- Applies to Planes only.
          P.Flob,   -- Applies to Planes only.
          U.FirstName AS OwnerFirstName,
          U.LastName  AS OwnerLastName
       FROM Item I
       JOIN Category IC
         ON I.CategoryCode = IC.CategoryCode
  LEFT JOIN Car C
         ON C.CarId = I.ItemId
  LEFT JOIN Boat B
         ON B.BoatId = I.ItemId
  LEFT JOIN Plane P
         ON P.PlaneId = I.ItemId               
       JOIN UserProfile U
         ON U.UserId = I.OwnerId;

--

此處顯示的視圖程式碼僅是說明性的。當然，做一些測試練習和修改可能有助於加速手頭查詢的（物理）執行。此外，您可能需要根據業務需求為所述視圖刪除或添加列。

範例數據和所有視圖定義都被合併到這個 db<>fiddle中，以便可以“在執行中”觀察它們。

數據操作：應用程式碼和列別名

應用程式碼的使用（如果這就是您所說的“伺服器端特定程式碼”）和列別名是您在接下來的評論中提出的其他重要點：

• 我確實設法解決了$$ a JOIN $$伺服器端特定程式碼的問題，但我真的不想這樣做 - 並且 - 為所有列添加別名可能是“壓力”。

• 解釋的很好，非常感謝。但是，正如我所懷疑的那樣，由於與某些列的相似性，我在列出所有數據時必須操縱結果集，因為我不想使用多個別名來保持語句更清晰。

有必要指出，雖然使用應用程式碼是處理數據集的表示或圖形特徵（即電腦化資訊系統的外部表示）的非常合適的資源，但最重要的是避免執行數據逐行檢索以防止執行速度問題。目標應該是通過 SQL 平台的（精確的）集合引擎提供的強大的數據操作工具來“獲取”相關的數據集，以便您可以優化系統的行為。

此外，在特定範圍內使用別名重命名一個或多個列可能看起來很緊張，但就我個人而言，我認為這種資源是一種非常強大的工具，有助於 (i) 上下文化和 (ii) 消除歸因於相關內容的含義和意圖列; 因此，對於感興趣的數據的操作，這是一個應該徹底思考的方面。

類似場景

您不妨在這一系列文章和這組文章中找到幫助，其中包含我對另外兩個案例的看法，其中包括超類型-子類型與互斥子類型的關聯。

我還為涉及超類型-子類型集群的業務環境提出了一個解決方案，其中子類型在這個（較新的）答案中並不互斥。

---

尾註

1 資訊建模集成定義( IDEF1X ) 是一種高度推薦的數據建模技術，於 1993 年 12 月由美國國家標準與技術研究院(NIST)確立為*標準。*它完全基於 (a) 由關係模型的唯一創始人，即EF Codd 博士撰寫的一些理論著作；(b)由PP Chen 博士開發的實體關係視圖；以及 (c) Robert G. Brown 創建的邏輯數據庫設計技術。

2在 IDEF1X 中，角色名稱是分配給 FK 屬性（或屬性）的獨特標籤，以表達它在其各自實體類型範圍內的含義。

3 IDEF1X 標準將鍵遷移定義為“將父實體或通用實體的主鍵作為外鍵放置在其子實體或類別實體中的建模過程”。

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