I'm having trouble generating a proper database design for bill of materials in EF Code-first
I just need a general structure then ill add additional fields
So i need to have a Product that is assembled from other Products which are assemblies of other Products.
Example:
Product 1:
-
Aluminum Box 1x
-
Aluminum Sheet 4x
-
Foil 1x
-
Screws 6x
-
Product 2:
-
Wheel Cart 1x
-
Aluminum Box 1x
-
Wheels 4x
-
Screws 4x
-
Foil 2x
-
So notice Product 2 (Wheel Cart) is assembled from Product 1 (Aluminum Box) and some additional components, yet same Product Screws and Foil is used in both.
So i think its hierarchical many to many. Or maybe someone knows a better solution.
Doesn't have to be in EF Code-first, i can re-generate code from T-SQL.
Thank you.
EDIT:
Ive been messing around and nothing seems to work,
i have this currently:
Components
ComponentID
Name
Price
ProductID
N
|
|
|
1
Products
ProductID
ParentID
Quantity
ComponentLookupId
First i create a Component, then a Product which can be hierarchical and tie a Component to it, and now i am fetching the Name and Price of the Component via Lookup, which is sort of OK, but having problems with summaries.
If i copy Name, Price then when Component is changed Product isn't.
Or should i try many-to-many with additional fields in a join table.
Best Answer
This concept is called a 'super BOM' its a BOM that is made up of other BOMs. You need a materials table, a BOM table, a BOMMaterials table and a SuperBOM table. The SuperBOM table represents relationships between BOMs.
Roughly as follows: