implement parent and child in build of materials “

Question

Post reply

implement parent and child in build of materials “

yogiberr

Hall of Fame

Points: 3679
More actions
June 24, 2003 at 3:49 am

#101535

Hi, I would like to implement a “build of materials “ for products.

I have the following tables:

tblProduct

productId PRIMARY KEY

productDescription

blah

tblBOM

bomId PRIMARY KEY

bomName

tblBomProduct

bomId REFERENCES tblBOM (bomId)

productId REFERENCES tblProduct (productId )

Obviously, for this to work, I need to be able to implemement a meaningful hierarchy.eg if the base product is a CAR, how do I ensure that I:

1) CAN add a WHEEL to a CAR’s build of materials

2) CANNOT ever add a CAR to a WHEEL’s build of materials?

3) CAN add a HUBCAP to a WHEEL’s build of materials?

4) CANNOT ever add a WHEELto a HUBCAP's build of materials?

So, I am asking how do I know that a particular product can potentially be added to another product’s build of materials?

Do I add a “parent” flag to the tables?

Please let me know if this explanation is dodgy.

many thanks, yogi

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

NPeeters SSChampion Points: 12358 More actions · Answer 1

Seems like near impossible to do. The only way you could enforce this, is by adding a 'BOM level' to the product table.

E.g. Car = 1, Wheel = 2, Hubcap = 3

You can only add something to the BOM if the level is lower than that of the BOM.

I think this will become very difficult to manage. Especially if you have one item that belongs to more than one other BOM.

E.g. a wheel belongs to a car, but it also belongs to a 'spare wheel set', which belongs to a car. This would mean that a car has level 1, the 'spare wheel set' level 2 and the wheel level 3. If you want to add this 'spare wheel set' somewhere down the line (because a new product becomes available), you are bound to run into trouble sooner or later.

yogiberr Hall of Fame Points: 3679 More actions · Answer 2

thanks for the reply bud.

As you've probably gathered, I'm pretty new to this sort of thing. I was previously considering:

1) store a description of each item that composes the base product's "build of materials"

2) access them by doing string searches on this description.

Surely this would be extremely time consuming and messy (manipulating strings etc),

I imagine that there must be a far more atomic way to achieve this.but obviously, i don't know what.

My suggestions are by no means set in stone (they are simply suggestions, probably naive ones)

Please, any "more informed" suggestions would be greatly welcomed.

cheers,

yogi

crosspatch Hall of Fame Points: 3108 More actions · Answer 3

OK this is the nub of Manufacturing control systems. You have to make a seperation in your mind between products and Bills Of Material (BOM's) You're most of the way there in identifying that you need a BOM table. You must also cater for the fact that a Product could have more than one BOM (some Chemical products are like this and don't let's even think about the Apparel trade where you have BOM's for seperate colourways and sizes.

In my experience this solution has worked for me. Devide you BOM into a header and detail record structure. In the header identify the final finished Product-ID BOM ID and any other stuff like descriptions, manufacturing-process ID's etc. Then your detail records. Well these will carry the BOM-ID (and the finished goods ID if you wish) a level ID is good too. Now a BOM level could contain EITHER a product (raw-material, bought-in part) OR another BOM for a manufactured part. Consider a front-axle (BOM 1) where the final product consists of a left wheel/brake assembly(BOM A) a right-hand wheel/brake assembly (BOM B) soem bolts to connect it all together (product ID's). You can further break it down in this model where the Wheel/Brake assemblies are themselves composed of sub-assemblies.

In short a typical BOM structure is Structure of Structures. In the example of the Front Axle. You would create seperate BOMs for the Wheel Assemblies which could be used on many different axles. When you construct an axle you build the BOM from both products and BOMs. Where do you think the Computer industry got the idea of Object Oriented Programming?

Phew hope that helps

I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

jpipes SSChampion Points: 12230 More actions · Answer 4

NPeeters is right on when he says this will become difficult to manage...Anyway, that said, you could implement a structure like so:



CREATE TABLE Product

(

  ProductID INT NOT NULL IDENTITYT(1,1)

, ProductDesc VARCHAR(150) NOT NULL

)

--

CREATE TABLE ProductDependent

(

  Product INT NOT NULL

, Dependent INT NOT NULL

)

--

CREATE TABLE BOM

(

  BOMID INT NOT NULL IDENTITY(1,1)

, BOMName VARCHAR(150) NOT NULL

)

--

CREATE TABLE BOMProduct

(

  BOM INT NOT NULL

, Product INT NOT NULL

)

--

CREATE TABLE BOMDetail

(

  BOM INT NOT NULL

, Product INT NOT NULL

)

--

-- You can access products and their

-- dependents via the ProductDependent

-- table and the BOMProduct (which would

-- store all the base Product IDs in the

-- BOM.  The BOMDetail table would then store

-- every Product tied to any base product:

CREATE PROC PopulateBOMDetail

  @BOM INT 

AS

BEGIN

--

INSERT INTO BOMDetail

SELECT @BOM, Product

FROM ProductDependent

INNER JOIN BOMProduct 

  ON ProductDependent.Product = BOMProduct.Product

WHERE BOMProduct.BOM = @BOM

UNION

SELECT @BOM, Dependent

FROM ProductDependent

INNER JOIN BOMProduct 

  ON ProductDependent.Dependent = BOMProduct.Product

WHERE BOMProduct.BOM = @BOM

GROUP BY @BOM, ProductDependent.Dependent

I have a feeling you're in for a rough ride...

NPeeters SSChampion Points: 12358 More actions · Answer 5

Yogi,

I believe you are on the right track with the proposed table structure. Of course, it needs some additional fields, but I suppose you only mentioned the important ones.

If I read your structure correct you would have a BOM in your tblBOM, let's say bomId = CAR.

In the tblBomProduct table, you would have the parts for that bom.

bomID productId

----- ---------

CAR CARBODY

CAR WHEEL

WHEEL TIRE

WHEEL HUBCAP

As you can see, the productID can contain both a product (a leaf in your assembly tree, like HUBCAP) or a reference to a BOM of it's own (like WHEEL). Therefore, it might be wise to include some sort of 'type' field to identify if it's an assembly or a 'leaf' product. Makes life a lot easier.

But this still does not solve your 'level' issue. My personal opinion would be to leave this up to the person filling up the table. If he makes a mistake, well, too bad ...

If you really want to do this check automatically, you will need some very careful planning when filling up the tables.

crosspatch Hall of Fame Points: 3108 More actions · Answer 6

Yup BOM's are complex to maintain, that's why Manufacturing Control packages want big bucks. However, I've worked (and designed) several MRP systems in my time including for the Apparel trade.

You can work without a level value in your records but it does make reporting and collation a little more difficult.

In actual fact prohibiting circular references is fairly straight forward if you hold your top-level product id in the BOM header, all you do is check that the product id/bom id a user is attempting to add to a structure is not already present in the BOM headers in the existing structure.

BOM maintenance is normally a relatively infrequent occurence so you can afford a little sluggishness.

However you must index the structure so that when it's used for builds, picking lists, purchase plans it responds quickly

I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961

yogiberr Hall of Fame Points: 3679 More actions · Answer 7

folks, thanks for all the suggestions.aye, I knew that this was going to be a bit hairy, but now i can make a uniformed decision.I will take a few days to get my head round this, and post a short review of what I did.Please feel free to laugh and or comment 🙂

i really appreciated you all taking the time.

many thanks,

yogiberr

yogiberr Hall of Fame Points: 3679 More actions · Answer 8

quote:

folks, thanks for all the suggestions.aye, I knew that this was going to be a bit hairy, but now i can make an informed decision.I will take a few days to get my head round this, and post a short review of what I did.Please feel free to laugh and or comment 🙂

i really appreciated you all taking the time.

many thanks,

yogiberr

stax68 SSChampion Points: 11711 More actions · Answer 9

quote:

Obviously, for this to work, I need to be able to implemement a meaningful hierarchy.eg if the base product is a CAR, how do I ensure that I:

1) CAN add a WHEEL to a CAR’s build of materials

2) CANNOT ever add a CAR to a WHEEL’s build of materials?

3) CAN add a HUBCAP to a WHEEL’s build of materials?

4) CANNOT ever add a WHEELto a HUBCAP's build of materials?

Looks like you should have a hierarchical structure representing TYPES of product and how they can be related as part/whole, and then use that for checking/templating your (user-defined) actual BOMs.

After all, I assume that the type TYRE will have different subtypes, which can't be related in advance of producing an actual BOM. So you will be working with broad types, which can then be referred to in enforcing valdity in a fully-specified BOM.

Make sense?

Tim

Tim Wilkinson

"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant

yogiberr Hall of Fame Points: 3679 More actions · Answer 10

hi folks,

here's my sickening rendition of what I have managed to achieve so far...

I have used the tables suplied by jPipes,...apologies if i have abused them.

I will use a "car" as a sample "finished product"

This car is composed of other products.

tblProduct (which contains ALL the products in my database)

productId productDesc

1 car

2 frontWheel

3 rearWheel

4 frontWheelBolt

5 rearWheelBolt

6 frontWheelWasher

7 rearWheelWasher

tblBOM

bomId bomName

1 frontWheelBOM

2 rearWheelBOM

I’ll list the products that compose the “frontWheelBOM"

tblBOMProduct

bomId productId

1 (frontWheelBOM) 4 (frontWheelBolt)

1 (frontWheelBOM) 6 (frontWheelWasher)

Now, I realise that I have to tie this “bomId” to the “productId” of the “parent” product, ie, the car.

Do I do it in the tblProductDependent, ie

tblProductDependent

productId dependentId

1 ( car) 1 (bomId for “frontWheelBOM”)

Have I followed everyone correctly so far?

Please bear with me, I appreciate the help and am doing my best to get my head round this.I realise that there are other issues that I will have to solve, however, I hope that I have interpreted the table structure correctly?

Thank God they don’t have visio enabled for this forum, I’d have rustled up a few spaghetti junctions by now 🙂

Cheers,

Yogiberr.

jpipes SSChampion Points: 12230 More actions · Answer 11

yogiberr: "spaghetti junctions"? no reference to the Atlanta roads system is it? Anyway, it looks like you've had a heck of a crack at a solution, and done fairly well. The only thing you're missing is that ProductDependent stores both Product IDs, not a Product ID and a BOM ID. So, ProductDependent should contain, for instance:



Product       Dependent

------------  ---------

1 (car)       2 (front wh)

1 (car)       3 (rear wh)

2 (front wh)  4 (front wh blt)

2 (front wh)  6 (front wh wsh)

4 (rear wh)   5 (rear wh blt)

4 (rear wh)   7 (rear wh wsh)

and so on...

HTH, and keep up the good work...

crosspatch Hall of Fame Points: 3108 More actions · Answer 12

The solution so far is great and you can represent multi-levels by recursively reading the BOMs so you could have

Car

---FRONT WHEEL

--------------Hub

--------------Tyre

--------------Bolts

---Rear Wheel

--------------Hub

--------------Tyre

--------------Bolts

A couple of things to consider, which though stating the obvious, are worth checking for anyway.

In all our examples none of us have mentioned quantity-off e.g 4 bolts to a wheel etc.

Secondly remember that a common product might appear in more than one place in a complex build. For example you may use the same bolts on both a front and a rear wheel. You could even use a common assembly in more than one place (an electric window mechanism for example). I mention it because I have worked on systems that have precluded the possibility through clumsy use of unique indexes.

I have found it is possible to please all of the people all of the time if you do exactly what they want. Harold Macmillan 1961