Bill of materials database design in EF codefirst?

Question

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.

Answer 1

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

Answer 2

You can store tree style data:

public class Item {
    [Id, DatabaseGenerated]
    public int Id {get;set;}
    [ForeignKey("Parent")]
    public int? ParentId {get;set;}

    //navigation to parent item
    virtual public Item Parent {get;set;}
    //navigation to children
    virtual public IEnumerable<Item> Children {get;set;}
}

where ParentId is a nullable column in the database with an appropriate foreign key constraint. If the ParentId is null, this item is at the top of a hierarchy. You can traverse the heirarchy through the navigation properties - though, with lazy loading enabled, this will generate a lot of little queries, so it may not be appropriate for you.

This doesn't address some of the other issues about BOM systems; for example, you may have an item that is used as a subassembly in one place but not all, so this isn't a complete solution, but it might address some of your question.