SQL Server – Hierarchical References Abstraction with Strict Model

database-designreferential-integritysql server

I want to abstract a real world hierarchy in a strict DB model (as strict as possible).

My problem is to to create a structure with a this-or-this-but-not-both rules set.

I have two entity types,

  • Device, should be connectable to one other "device" as child and as parent.

  • Combiner, it can be connected with multiple "devices" as parents and one as child.

BUT(!) a device can only be connected to one of them at the same time. So like "device" as only one child and one parent.

I try to visualize it somehow:

pattern

All my ideas are not strict but I searching if there is a pattern to solve this without rules on application level. On a class model I would solve this with inheritance. Some ideas?

Best Answer

Here is a possible solution. Navigating the structure from top to bottom may be a little gross but maybe the app side objects can handle it.

I think you can build this out using 2 tables dbo.Device and dbo.Combiner. Each table has a column that references who its parent object is, regardless of whether it is a device or a combiner. We can then use a couple different types of constraints and a couple of triggers to ensure that the data follows the path you are looking to use.

Table Structures (now your objects probably have more data points than just a name but for the sake of simplicity I am only going to keep track of a name)

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

Each table holds who its parent object is via ParentDeviceID or ParentCombinerID. The Unique Filtered Index on ParentDeviceID helps ensure that a given Device can only be the parent of one object (A Unique Constraint would stop multiple rows with a NULL value). It isn't guaranteed so that is why we are going to use some triggers down the road. The two CHECK Constraints help ensure that any given object can only have 1 parent object instead of having both a Device and Combiner as a parent. The Foreign Keys ensure that the values stored in ParentDeviceID and ParentCombinerID are valid values for those two tables.

Triggers

We can insert a Trigger on each table. These Triggers need to ensure:

  1. On the insert/update of a dbo.Device record a value stored on ParentDeviceID is not already on dbo.Combiner.ParentDeviceID
  2. On the Insert/Update of a dbo.Combiner record a value stored on ParentDeviceID is not already on 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

As an alternative this StackOverflow question apperently give the same or similar effect with a Unique Index on a View. If you want to avoid Triggers than this may be a solution for you. https://stackoverflow.com/questions/16314372/ms-sql-server-cross-table-constraint. I am not familiar with this approach but maybe it is a better solution for you.

Examples

Implementing the 1st Example provided (just 3 devices in a chain) would look something like this. Just 3 records in dbo.Device:

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

Implementing the 2nd Example provided (1 Device --> 1 Combiner --> 3 Devices) would look something like this. 4 Records in dbo.Device and 1 Record in 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             |
+----+------+----------------+------------------+

One more, slightly more complicated example to display all the relationship options: Slightly More Involved Example

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                |
+----+------+----------------+------------------+

Hopefully this give you all you need from the database side. Again I think this will be complicated on the App side navigating the table structures. But the database should keep the relationships the way you want to.

Related Question