Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Dec 8 '11 at 15:50

This question came from our site for professional and enthusiast programmers.

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

2 Answers 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
share|improve this answer
    
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
1  
maybe calling them Subassembly and FinalAssembly would be clearer –  Mikeb Dec 7 '11 at 20:06
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.