Solved Products - Sub Assemblies - Assemblies

[CJ_London]

Its been a while but I wrote a system many years ago - we used the same principle as outlined by arnel

 

[MajP]

I don't see using a self referencing table for this application. That only works if a part can only be assigned to a single Assembly.

Are you talking about a generic part or a discrete part? If the latter can you give an example how a specific physical part can be a member of multiple assemblies?
If you are talking a generic part, then this can be done easily in a self referencing table as I demoed in the BOM example where you can make many BOMs from a family of parts. Simply host a foreign key to the parent product. So generic part (Part ABC123) can go to many assemblies/subassemblies. If we are talking a discrete physical part (Part ABC123, ID 456789), how does a discrete item belong to multiple assemblies? Trying to picture this. Never seen system's engineering do something like that.

 

[Pat Hartman]

I'm confused Maj. How does an item have more than one FK? What does "Simply host a foreign key to the parent product." Where are the FKs "hosted" if not in a junction table?

 

[MajP]

In that demo I had a list of generic parts
tblParts tblParts

PartID	PartNumber	PartName	PartSerial	PartCategory
28​	82476	Stage Final		826877
31​	826233	Stage Final		Assembly
29​	824762	Stage 5 Assembly		Assembly
13​	825514	PBA
30​	818222	New Part
26​	825037	Manifold
8​	816539	IAP
1​	824771	DI
18​	812082	82 Part
20​	812081	81 Part

I then Create a BOM.
tblBOMs tblBOMs

BOM_ID	BOM_Name	BOM_Date	BOM_Description
1​	BOM Demo 1 (824761)	3/27/2023​
2​	BOM Demo 2	3/28/2023​	This is another demo BOM wit Stage Final 826233

Now I add the items to a BOM. By placing them in assemblies/sub-assemblies
tblBOM_Items tblBOM_Items

BOM_Item_ID	PartID_FK	ParentPart_ID_FK	BOM_ID_FK
260​	28​	​	1​
261​	30​	​	1​
262​	26​	​	1​
263​	31​	​	1​
264​	9​	260​	1​
265​	5​	263​	1​
266​	19​	263​	1​
267​	12​	262​	1​
268​	9​	​	1​
269​	11​	265​	1​
270​	5​	267​	1​
271​	23​	270​	1​
272​	19​	261​	1​
273​	7​	272​	1​
274​	20​	​	1​
275​	20​	​	1​

Every Part has a FK relating it to a specific BOM. Every Part has an FK relating to a single Parent assembly/sub-assembly.
So generic Part 9 has a Parent of Part 260 in BOM 1. Generic Part 9 is also a component at the top level of the BOM. So part 9 can be part of many assemblies/sub-assemblies within different BOMs. But specific Item 274 can only have one parent assembly.

Now what is probably missing is relations already established in the Parts table creating sub assemblies. It would be unrealistic each time to have to build each subassembly by adding all the child parts. Instead you would pick an assembly/subassembly from the Parts table to add to the BOM and it would pull in all child records. So there is a self referencing parts reference table and a self referencing BOM data table.

 

[Pat Hartman]

OK, so you are using a junction table. That is what I was trying to figure out. It sounded like magic from your initial description. Thanks.

 

[KitaYama]

@MajP I've seen a lot of times BOM has been brought up in different threads and to my knowledge, I've found everyone has a wrong impression of BOM. Before I go further, let me give you an example of a BOM. Because I think we have a problem in our terminologies.

First of all, BOM is the abbreviated form of : Bill Of Material. But what does it mean? According to any article in google search:
A bill of materials (BOM) is a centralized source of information used to manufacture a product. It is a list of the items needed to create a product. A bill of materials or product structure (BOM) is a list of the raw materials, sub-assemblies, intermediate assemblies, sub-components, parts, and the quantities of each needed to manufacture an end product.

In any Cad or Cam application when you click "Create BOM", it gives you a list of parts, components & sub assemblies used in that specific design.
I just made a simple assembly to show you how it is:

The above BOM, shows an assembly that is made of two sub assemblies (items 1 & 2) and a single part (item 3).
The blue circles shows that the same part is used in both sub assemblies.

This is a real BOM. Having that in your mind, would you please explain :

1. What does tblBoms means in your database? It's a table just giving BOM1, Bom2, BOM3. I don't see it as a BOM. Because a BOM is a list not a name. Do you mean that tblBoms is a table that keeps the name of all Top Assemblies? If so, as you know better, a top assembly is a product too. Isn't it? If it's a product, does it mean you have 2 tables for products? (tblParts and tblBoms). I don't see BOM1 etc in your tblParts? Why assemblies or sub assemblies are not in your product (part) table? (I'm not arguing. I'm just trying to understand the rules to follow your solution.)
   
   
2. How does a generic part is different with a discrete part? A company manufactures a lot of parts. Some of them are used in different assemblies. Just imagine a company that manufactures cars. The car is the top assembly. This company manufactures an O-Ring and this O-Ring may be used in different sub assemblies. It's used in carburetor, Fuel pipes, transmission box etc. The same washer may have been almost in all sub assemblies in a car. A bolt, a screw, a pipe, a gear ...... they have used in many sub assemblies.
   So what makes a generic part different with a discreet part in your application? Can you give a real world explanation? For example in this assembly, this is generic, this is discreet.

can you give an example how a specific physical part can be a member of multiple assemblies?

Your car uses the same brake pad for front and rear brakes. But since the structure of the body that holds the pads are different, they design two different brake house. So the same brake pad (a product or part) is used in two different sub assemblies (Front & rear BrakeS)

In above BOM image, KO-Boss-03 is a bush with a specific size. It's been used twice in two different sub assemblies. In a real world production environment, an organization tries to bring down the manufacturing cost by using the same product over and over in different sub assemblies.
Manufacturing a product with a mass quantity always is cheaper than manufacturing multiple parts with low quantity. If you dis-assemble the tower of your PC, you'll find the similar parts are used in different sub assemblies. My Dell Precision 7520 has used the same motor, for 3 different fan assemblies. CPU, Memory and GPU.
In a bike, the same screw can be used in many sub assemblies.
In general, the following parts will be used over and over in different subassemblies:
Bolts and screws. Bearings. Springs. Cables and wiring harnesses. Valves. Caskets and seals. Pumps and compressors. Hinges and Latches. Filters.....


I really appreciate your time for helping.

 

[The_Doc_Man]

Genealogy ought to be much simpler in principle than the product structures you are considering, as any person has just one father and one mother.

One of my step-daughters would blow up that theory since genealogy is more than just sperm and egg donors. My daughter M has:

1. The man who was married to her mother at the time of her birth
2. The biological father who fled the scene
3. Her adopted father who didn't want to break up the family, and M was technically his niece
4. Her step-father (ME) who married M's adopted mother and natural aunt.

Trust me, more dotted lines than European Medieval royalty diagrams.

 

[KitaYama]

I just wanted to add, a BOM can have different formats. But in any case, it is a LIST of components (parts and parts in subassemblies) that is used to manufacture a product.

The following is when I choose to hide sub assemblies names and show only the used parts.
As you see the part that in #26 assembly had been used in two different sub assembly, now has the total quantity as 4.

 

[arnelgp]

sample BOM that i used to handle (only SCR materials, there are other Boiler materials I am responsible such as ESP, etc).

 

[gemma-the-husky]

One of my step-daughters would blow up that theory since genealogy is more than just sperm and egg donors. My daughter M has:

1. The man who was married to her mother at the time of her birth
2. The biological father who fled the scene
3. Her adopted father who didn't want to break up the family, and M was technically his niece
4. Her step-father (ME) who married M's adopted mother and natural aunt.

Trust me, more dotted lines than European Medieval royalty diagrams.

You've mentioned this before, but I submit that's something other than genealogy. Modelling a family isn't the same as as modelling parentage, if you will.

 

[gemma-the-husky]

Re the parts explosion. I imagine a choice you need to make is how to explode the hierarchy of sub assemblies.

In a really complicated structure, I imagine there are multiple sub assemblies built into other sub assemblies.

One way (the only way? ) to model this is to define the parts explosion of each sub assembly in a separate table.

I also imagine once you build a sub assembly, you wouldn't cannibalize it for it's constituent parts, although you may want to know what the parts are.

If you produce a parts listing you might want to know there are 76 particular screws in the completed product for ordering purposes without needing needing to know specifically how those screws are incorporated into the structure of the whole. It depends what you are doing though. The recursive analysis should be able to generate what you need for any particular function though.

You may need to iterate subassemblies until you reach final parts, and store those parts in a table for subsequent reporting. Something like that.

I imagine @MajP has it covered.

 

[ebs17]

@KitaYama
Take a look at this example from Josef P.

 

[MajP]

@MajP I've seen a lot of times BOM has been brought up in different threads and to my knowledge, I've found everyone has a wrong impression of BOM. Before I go further, let me give you an example of a BOM. Because I think we have a problem in our terminologies

I am pretty sure we are talking the same thing. Maybe instead take look at the application and video that demos it? Then make up your mind if this can support what you want to da. I am not seeing what this structure does not support. It would support the creations of different levels of BOMs
.

Again this is all done by dragging and dropping parts into assemblies and sub assemblies.

What is missing is that the parts table should include pre made subassemblies. So instead of having to build the sub assemblies as you build the BOM your drag either a single part or a complete sub assembly. That is a trivial addition.

 

[KitaYama]

I am pretty sure we are talking the same thing. Maybe instead take look at the application and video that demos it? Then make up your mind if this can support what you want to da. I am not seeing what this structure does not support. It would support the creations of different levels of BOMs
.


Again this is all done by dragging and dropping parts into assemblies and sub assemblies.

What is missing is that the parts table should include pre made subassemblies. So instead of having to build the sub assemblies as you build the BOM your drag either a single part or a complete sub assembly. That is a trivial addition.

Thanks for taking your time and putting that up.
I understand what you're doing or what you were doing. I hoped you had answered my question.

Two simple question because it seems that I wasn't able to be clear enough.

1. In your last demo, KitaYamaDemo is the end product. It means it's the top assembly. It's the final product that's going to be sent to a customer.
   Am I correct that the top assembly (even if it's a product) should be in a different table than tblProducts? (which you named it tblParts)
   Am I correct if I assume you have a table for parts (tblParts) and a table for assemblies (tblBoms)?
   
   
2. What will happen if one month from now, we design a new assembly that needs the whole KitaYamaDemo be used as a sub assembly? At any point, any assembly can be used as a sub assembly for a newly designed higher level assembly. Is there an easy way to add a current assembly to another assembly?
   Example : In your current structure, KitaYamaDemo is the top assembly and is located in tblBoms. AL11713BU101 & AL15777BP202 (even if they are sub assemblies are treated as a part and are saved in tblParts.
   What will happen if someday we design a higher level assembly that needs KitaYamaDemo to be used as a sub assembly? Should I save KitaYamaDemo in tblParts too? Because now it will be used as a sub assembly? (Just like AL11713BU101 that's a sub assembly).
   Doesn't it conflicts with how your structure works? (Asking. Not suggesting)

I sincerely appreciate your patient on this.

 

[KitaYama]

@KitaYama
Take a look at this example from Josef P.

Thanks. Give me a little while to see how your design works.

 

[MajP]

• In your last demo, KitaYamaDemo is the end product. It means it's the top assembly. It's the final product that's going to be sent to a customer.
  Am I correct that the top assembly (even if it's a product) should be in a different table than tblProducts? (which you named it tblParts)
  Am I correct if I assume you have a table for parts (tblParts) and a table for assemblies (tblBoms)?

That was not the intent. The concept you Build a BOM consisting of a Top Assembly and all subassemblies and their parts. The BOM itself could be simpy something like Draft Proposal 1. Final Proposal 2. It could maybe be used as you described, but if the top assembly should be in Parts table.

• What will happen if one month from now, we design a new assembly that needs the whole KitaYamaDemo be used as a sub assembly? At any point, any assembly can be used as a sub assembly for a newly designed higher level assembly. Is there an easy way to add a current assembly to another assembly?
  Example : In your current structure, KitaYamaDemo is the top assembly and is located in tblBoms. AL11713BU101 & AL15777BP202 (even if they are sub assemblies are treated as a part and are saved in tblParts.
  What will happen if someday we design a higher level assembly that needs KitaYamaDemo to be used as a sub assembly? Should I save KitaYamaDemo in tblParts too? Because now it will be used as a sub assembly? (Just like AL11713BU101 that's a sub assembly).
  Doesn't it conflicts with how your structure works? (Asking. Not suggesting)

As I said this part has not been flushed out; however, the structure exists to do this. Currently you build the whole thing by grabbing individual parts and forming into assemblies/sub assemblies. I did not demonstrate the ability to create as sub assembly and then be able to reuse the entire sub assembly in another assembly or another BOM.

So what I think I need to demo is the ability to create a sub assembly like AL117.. and save that structure to the Parts list. Then allow you in the future pick AL117... entire assembly and add it to something else. If AL117... is a sub assembly of something bigger than allow you to save the bigger thing too.

Assume the thing you save has eight nested levels. You may want to pick the entire thing and add it to something bigger. Or you may want to pull any level of the subassemblies and pull that into something else.

 

[Pat Hartman]

All parts belong in a single table. If you separate them, you end up with a maintenance problem should one of the assemblies become an end item. The junction table builds the relationships which is how you derive the indented list.

There were two other concepts in the 747 BOM and those were procurement codes and low-level codes. The procurement code had about 8 options, most of which escape me now but essentially it specified whether an assembly was bought or built. The low-level coded indicated the lowest level in the hierarchy where a part occured. 1 = end items. Most assemblies occured at only a single level but detail parts could repeat at multiple levels and so they were coded with their lowest level. I'm fuzzy but I think this code was used by the shop floor scheduler to ensure that parts arrived when they should to the assembly line.

 

[KitaYama]

@KitaYama
Take a look at this example from Josef P.

@ebs17 I appreciate showing your way how to solve this case.
It seems your solution is the same as what @arnelgp suggested in #6 and @Pat Hartman in #17.

Thanks to all who shared their experience to help. It seems that I have two possible way to solve this problem.
Having a junction table that both sides are tblProducts,
or
Keeping the assemblies in a separate table than products (tblBoms) and add a table that keeps the items used in assemblies (tblBom_Items), then use it as junction table between itself and tblProducts. (though I still have to find a way to link tblBoms to tblProducts too to gain a Referential integrity, because assemblies are products too)

I'll do some testing to see which one suits us better and which one we can work on. For now I mark the thread as solved.
Once again million thanks for your help and concern.

cheers.