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.