4

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.

1
  • So you want to store a recursive data structure in SQL?
    – Roman
    Dec 7, 2011 at 14:36

2 Answers 2

2

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
2
  • 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, 2011 at 19:39
  • 2
    maybe calling them Subassembly and FinalAssembly would be clearer
    – Mikeb
    Dec 7, 2011 at 20:06
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.