C# – Bill of materials database design in EF codefirst

ccodefirstsqltsqlvb.net

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:

Material
-------------------------------
ID     Description

BOM
-------------------------------
ID     Description


BOMMaterials
-------------------------------
BOMID     MaterialID

SuperBOM
-------------------------------
ParentBOMID     ChildBOMID
Related Topic