Sql-Server

具有嚴格模型的分層引用抽象

  • July 22, 2019

我想在嚴格的 DB 模型中抽像出真實世界的層次結構（盡可能嚴格）。

我的問題是要創建一個具有 this-or-this-but-not-both 規則集的結構。

我有兩種實體類型，

  • Device，應該可以作為孩子和父母連接到另一個“設備”。
  • 組合器，它可以連接多個“設備”作為父母和一個作為孩子。

但是（！）一個設備只能同時連接到其中一個。所以像“設備”一樣只有一個孩子和一個父母。

我嘗試以某種方式將其視覺化：

圖案

我所有的想法都不嚴格，但我正在尋找是否有一種模式可以在沒有應用程序級別規則的情況下解決這個問題。在類模型上，我會通過繼承來解決這個問題。一些想法？

這是一個可能的解決方案。從上到下導航結構可能有點粗略，但也許應用程序端的對象可以處理它。

我認為您可以使用 2 個表dbo.Device和dbo.Combiner. 每個表都有一個列，該列引用其父對像是誰，無論它是 adevice還是 a combiner。然後，我們可以使用幾種不同的類型constraints來triggers確保數據遵循您要使用的路徑。

表結構（現在您的對象可能有更多的數據點，而不僅僅是 aname，但為了簡單起見，我只會跟踪 aname）

dbo.Device

CREATE TABLE dbo.Device
(
   ID INT IDENTITY(1,1) NOT NULL,
   Name VarChar(100) NOT NULL,
   ParentDeviceID INT NULL,
   ParentCombinerID INT NULL,
   CONSTRAINT [PK_Device] PRIMARY KEY CLUSTERED 
   (
       [ID] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX IX_UniqueParentDeviceIDDevice 
ON dbo.Device(ParentDeviceID)  
WHERE ParentDeviceID IS NOT NULL

ALTER TABLE [dbo].[Device]  WITH CHECK ADD  CONSTRAINT [FK_Device_Device] FOREIGN KEY([ParentDeviceID])
REFERENCES [dbo].[Device] ([ID])
GO

ALTER TABLE [dbo].[Device]  WITH CHECK ADD  CONSTRAINT [FK_Combiner_Device] FOREIGN KEY([ParentCombinerID])
REFERENCES [dbo].[Combiner] ([ID])
GO

ALTER TABLE [dbo].[Device]
ADD CONSTRAINT CHK_Device CHECK (ParentDeviceID IS NULL OR ParentCombinerID IS NULL)
GO

dbo.Combiner

CREATE TABLE dbo.Combiner
(
   ID INT IDENTITY(1,1) NOT NULL,
   Name VarChar(100) NOT NULL,
   ParentDeviceID INT NULL,
   ParentCombinerID INT NULL,
   CONSTRAINT [PK_Combiner] PRIMARY KEY CLUSTERED 
   (
       [ID] ASC
   )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]

CREATE UNIQUE NONCLUSTERED INDEX IX_UniqueParentDeviceIDCombiner
ON dbo.Combiner(ParentDeviceID)  
WHERE ParentDeviceID IS NOT NULL

ALTER TABLE [dbo].[Combiner]  WITH CHECK ADD  CONSTRAINT [FK_Combiner_Device] FOREIGN KEY([ParentDeviceID])
REFERENCES [dbo].[Device] ([ID])
GO

ALTER TABLE [dbo].[Combiner]  WITH CHECK ADD  CONSTRAINT [FK_Combiner_Combiner] FOREIGN KEY([ParentCombinerID])
REFERENCES [dbo].[Combiner] ([ID])
GO

ALTER TABLE [dbo].[Combiner]
ADD CONSTRAINT CHK_Combiner CHECK (ParentDeviceID IS NULL OR ParentCombinerID IS NULL)
GO

ParentDeviceID每個表都通過或保存其父對像是誰ParentCombinerID。Unique Filtered IndexonParentDeviceID有助於確保給定對Device像只能是一個對象的父對象（AUnique Constraint將停止具有NULL值的多行）。不能保證，這就是為什麼我們將在未來使用一些觸發器。這兩個CHECK Constraints 有助於確保任何給定對像只能有 1 個父對象，而不是同時具有 aDevice和Combiner作為父對象。Foreign Keys 確保儲存在ParentDeviceID和中的ParentCombinerID值是這兩個表的有效值。

Triggers

我們可以Trigger在每個table. 這些Trigger需要確保：

  1. 在記錄的insert/update上，dbo.Device儲存的值ParentDeviceID尚未打開dbo.Combiner.ParentDeviceID
  2. 在記錄的Insert/Update上，dbo.Combiner儲存的值ParentDeviceID尚未打開dbo.Device.ParentDeviceID
-- On the Insert/Update of a dbo.Combiner record a value stored on ParentDeviceID is not already on dbo.Device.ParentDeviceID
CREATE TRIGGER dbo.CombinerCheck ON dbo.Combiner
AFTER INSERT, UPDATE
AS
BEGIN
   IF EXISTS
   (
       SELECT *
       FROM INSERTED I
           INNER JOIN Device D
               ON I.ParentDeviceID = D.ParentDeviceID
   )
   BEGIN
       RAISERROR ('This Combiner cannot be added since it''s parent Device is already a parent to a different Device.', 16, 1);
       ROLLBACK TRANSACTION; --stops the Insert/Update
       RETURN 
   END
END

--On the insert/update of a dbo.Device record a value stored on ParentDeviceID is not already on dbo.Combiner.ParentDeviceID
CREATE TRIGGER dbo.DeviceCheck ON dbo.Device
AFTER INSERT, UPDATE
AS
BEGIN
   IF EXISTS
   (
       SELECT *
       FROM INSERTED I
           INNER JOIN Combiner C
               ON I.ParentDeviceID = C.ParentDeviceID
   )
   BEGIN
       RAISERROR ('This Device cannot be added since it''s parent Device is already a parent to a Combiner.', 16, 1);
       ROLLBACK TRANSACTION; --stops the Insert/Update
       RETURN
   END
END

作為替代方案，這個 StackOverflow 問題顯然與 aUnique Index上的 a給出了相同或相似的效果View。如果您想避免Triggers，這可能是您的解決方案。 https://stackoverflow.com/questions/16314372/ms-sql-server-cross-table-constraint。我不熟悉這種方法，但也許它對你來說是一個更好的解決方案。

例子

實現提供的第一個範例（鏈中只有 3 個設備）看起來像這樣。中只有 3 條記錄dbo.Device：

+----+------+----------------+------------------+
| ID | Name | ParentDeviceID | ParentCombinerID |
+----+------+----------------+------------------+
|  1 | D1   | NULL           | NULL             |
|  2 | D2   | 1              | NULL             |
|  3 | D3   | 2              | NULL             |
+----+------+----------------+------------------+

實現提供的第二個範例（1 個設備 –> 1 個組合器 –> 3 個設備）看起來像這樣。4 條記錄dbo.Device和 1 條記錄在dbo.Combiner.

Device
+----+------+----------------+------------------+
| ID | Name | ParentDeviceID | ParentCombinerID |
+----+------+----------------+------------------+
|  1 | D1   | NULL           | NULL             |
|  2 | D2   | NULL           | 1                |
|  3 | D3   | NULL           | 1                |
|  4 | D4   | NULL           | 1                |
+----+------+----------------+------------------+

Combiner
+----+------+----------------+------------------+
| ID | Name | ParentDeviceID | ParentCombinerID |
+----+------+----------------+------------------+
|  1 | C1   |              1 | NULL             |
+----+------+----------------+------------------+

還有一個稍微複雜一點的例子來顯示所有的關係選項： 稍微多一些的例子

dbo.Device
+----+------+----------------+------------------+
| ID | Name | ParentDeviceID | ParentCombinerID |
+----+------+----------------+------------------+
|  1 | D1   | NULL           | NULL             |
|  2 | D2   | 1              | NULL             |
|  3 | D3   | NULL           | 1                |
|  4 | D4   | NULL           | 1                |
|  5 | D5   | NULL           | 1                |
|  6 | D6   | NULL           | 2                |
|  7 | D7   | NULL           | 2                |
|  8 | D8   | NULL           | 2                |
+----+------+----------------+------------------+

dbo.Combiner
+----+------+----------------+------------------+
| ID | Name | ParentDeviceID | ParentCombinerID |
+----+------+----------------+------------------+
|  1 | C1   | 2              | NULL             |
|  2 | C2   | NULL           | 1                |
+----+------+----------------+------------------+

希望這可以為您提供數據庫方面所需的一切。我再次認為這在應用程序端導航表結構會很複雜。但是數據庫應該以您想要的方式保持關係。

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

相關問答

Sql-Server

參照完整性——“深度”中的間接外鍵

  • May 15, 2020
檢視問答
Sql-Server

包含自引用表的複雜刪除級聯

  • July 9, 2019
檢視問答
Sql-Server

如何在多個表之間建模非常靈活的引用關係？

  • January 9, 2019
檢視問答
Sql-Server

避免循環/多個更新路徑的首選設計

  • September 1, 2017
檢視問答
Sql-Server

當兩個 FK 列需要在同一個表中匹配自己時的引用完整性？

  • April 29, 2017
檢視問答
Sql-Server

如何從大型 ER 模型中選擇數據子集？

  • August 11, 2016
檢視問答