Systems Engineering and RDBMS

Designing Physical tables for a M:N recursive relationship within an Entity

Posted by decipherinfosys on April 25, 2008

This requirement is pretty common in some of the industries: Many times, the same entity can have a recursive M:N (Many-to-Many) within itself.  For example:

In the pharmaceutical industry, a drug can be composed of many drugs and vice-versa.  Likewise, in a manufacturing industry, a part can be composed of many parts and vice-versa.  The way to model this logical design into it’s corresponding physical implementation is by splitting the entity out into two physical tables with two 1-n (one-to-many) relationship between the main table and the child table.  This table will only be used to capture the recursive relationship and any other attributes that are specific to that relationship.

Example:

/*********************************************************************************
We will only consider 2 attributes for the sake of the explanation
**********************************************************************************/
CREATE TABLE dbo.DRUG_MASTER (DRUG_MASTER_ID INT NOT NULL IDENTITY, DRUG_NAME NVARCHAR(100), CONSTRAINT PK_DRUG_MASTER PRIMARY KEY (DRUG_MASTER_ID));
CREATE TABLE dbo.DRUG_RLTN (PARENT_DRUG_ID INT NOT NULL, CHILD_DRUG_ID INT NOT NULL, CONSTRAINT PK_DRUG_RLTN PRIMARY KEY (PARENT_DRUG_ID, CHILD_DRUG_ID));

And we will now have two Foreign Keys from the child to the parent table to help support the two 1:N relations that we have put into place:

ALTER TABLE dbo.DRUG_RLTN ADD CONSTRAINT FK_DRUG_RLTN_PARENT_TO_DRUG_MASTER FOREIGN KEY (PARENT_DRUG_ID) REFERENCES dbo.DRUG_MASTER (DRUG_MASTER_ID),
CONSTRAINT FK_DRUG_RLTN_CHILD_TO_DRUG_MASTER FOREIGN KEY (CHILD_DRUG_ID) REFERENCES dbo.DRUG_MASTER (DRUG_MASTER_ID)
GO

Let us insert some data now to show how this all ties up:

SET NOCOUNT ON
GO
SET IDENTITY_INSERT DRUG_MASTER ON
INSERT INTO dbo.DRUG_MASTER (DRUG_MASTER_ID, DRUG_NAME) VALUES (1, ‘DRUG A’);
INSERT INTO dbo.DRUG_MASTER (DRUG_MASTER_ID, DRUG_NAME) VALUES (2, ‘DRUG B’);
INSERT INTO dbo.DRUG_MASTER (DRUG_MASTER_ID, DRUG_NAME) VALUES (3, ‘DRUG C’);
INSERT INTO dbo.DRUG_MASTER (DRUG_MASTER_ID, DRUG_NAME) VALUES (4, ‘DRUG D’);
INSERT INTO dbo.DRUG_MASTER (DRUG_MASTER_ID, DRUG_NAME) VALUES (5, ‘DRUG E’);
SET IDENTITY_INSERT DRUG_MASTER OFF

INSERT INTO dbo.DRUG_RLTN (PARENT_DRUG_ID, CHILD_DRUG_ID) VALUES (1, 2);
INSERT INTO dbo.DRUG_RLTN (PARENT_DRUG_ID, CHILD_DRUG_ID) VALUES (1, 3);
INSERT INTO dbo.DRUG_RLTN (PARENT_DRUG_ID, CHILD_DRUG_ID) VALUES (2, 5);
INSERT INTO dbo.DRUG_RLTN (PARENT_DRUG_ID, CHILD_DRUG_ID) VALUES (5, 4);

As you can see from above, the M:N relationship is being captured in the relationship table DRUG_RLTN.

  • DrugA comprises of DrugB and DrugC
  • DrugB comprises of DrugE, and
  • DrugE comprises of DrugD

In the next post, we will consider how you go about traversing this hierarchy using SQL and will use this as an example.  We will also use the classic employee structure in an organization as an example to show how to traverse hierarchies in SQL Server, Oracle and DB2.

One Response to “Designing Physical tables for a M:N recursive relationship within an Entity”

  1. […] Designing Physical tables for a M:N recursive relationship within an Entity […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: