BOM - multiple child to one parent relationship-CodePudding

i have the following table structure (dynamic hierarchy structure) for a bill of materials (BOM):

ID	ParentID	BOMLevel	BOMType	Name
1	NULL	1	EndProduct	PName1
2	NULL	1	EndProduct	PName2
3	1	2	Assemblies	AsseName1
4	1	2	Assemblies	AsseName2
5	2	2	Assemblies	AsseName3
6	3	3	SubAssemblies	SubAsseName1
7	4	3	SubAssemblies	SubAsseName2
8	6,7	4	RawMaterial	RawMName1
9	6,7	4	RawMaterial	RawNName1

How can i connect one child to many parents? here e.g. both raw materials are used in both SubAssemblies.

CodePudding user response：

You have some many-to-many relationship between your BOM entities.

That means you need a join table -- also known as a self-to-self bridge table -- to represent them. Let's call it BOM_PARENT. It has a definition like this:

CREATE TABLE BOM_PARENT (
  ID int NOT NULL,
  ParentID int NOT NULL,
  PRIMARY KEY (ID, ParentID),
  UNIQUE KEY (ParentID, ID)
);

When a row is present in this table, it means a BOM item has a particular parent BOM item. Thus, it's possible for a BOM item to have multiple parents, and for a parent item to have multiple children (careful with that; your BOMs otherwise may become tangled up).

In this case you remove the ParentID from your BOM table.

This query gets you your original data. Fiddle here.

SELECT BOM.ID, 
       GROUP_CONCAT(BOM_PARENT.ParentID) Parents,
       BOM.BOMLevel, BOM.BOMType, BOM.Name
  FROM BOM
  LEFT JOIN BOM_PARENT ON BOM.ID = BOM_PARENT.ID
 GROUP BY  BOM.ID, BOM.BOMLevel, BOM.BOMType, BOM.Name