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.


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.


Ive been messing around and nothing seems to work, i have this currently:
















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.

  • So you want to store a recursive data structure in SQL? – R0MANARMY Dec 7 '11 at 14:36

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:

ID     Description

ID     Description

BOMID     MaterialID

ParentBOMID     ChildBOMID
  • Ok, so Material and BOM are many-to-many on BOMMaterials, hows SuperBOM connected? Or it isnt at all? Then how do i display data. – Seeker Dec 7 '11 at 19:39
  • 2
    maybe calling them Subassembly and FinalAssembly would be clearer – Mikeb Dec 7 '11 at 20:06

You can store tree style data:

public class Item {
    [Id, DatabaseGenerated]
    public int Id {get;set;}
    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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy