Looking for some direction.

[MajP]

I am realizing that the Tree View works well for navigation, but would not be our preferred method for input/queries.
Too many mouse clicks.

That depends on your data. I can demo some structures that adding a node and moving a node would be way faster then any other method.
Treeviews work well and show utility when they have several levels and relatively few items per level. They really show utility when amount of levels is relatively many, variable, and unknown.
A tree of 100k items with about 5 levels and about 10 child items per level would be super useable. The same amount of nodes only 2 levels deep would be pretty unusable.
In the E2E demo, I show how to add, delete, and. Move a node. To include moving a whole branch. That would be nearly impossible to do another way.

 

[Pat Hartman]

This sounds slight different from the example given by Minty & MajP, using 2x tables instead of 1x.

The difference between 1 table and 2 is that the 1 table solution supports a 1-many relationship whereas the 2 table solution supports a m-m relationship. Let's take it down to detail parts. Are screws used in multiple assemblies or only ONE? They are used in many. So, the real world is almost certainly m-m or could be even if it is not today as your product line expands.

Even for serialized parts, you have to support m-m. While a specific hard drive cannot be installed in multiple PC's the hard drive "part" can be installed in different model PC's not just a single model. The BOM is not the same as a finished end item. That may be where the confusion arises. In that case, a specific screw, which is not even serialised, can only be installed as part of ONE physical PC.

The BOM for the 747 came in three flavors.
1. As designed - the engineering drawings
2. As planned - building the plane - working with what we have available so potential substitutions
3. As built - a real plane rolling out the door - with serial numbers applied as necessary.

 

[MajP]

I can demo this if I can decipher it.
Here is a cut away section to try to get some clarity.

Each box is an assembly of serialized parts.
The header contains: Assembly Part Number, Assembly Name, Record Document Part Number

Below the header is the list of serialized components that make up the assembly.
Red - Serialized part with no sub components.
Blue - Assembly with serialized sub components.
Green - Assembly with serialized sub components. (But there is no input on the record to make the relationship, this is for my management.)

Questions:

1. Is "Stage Final" the name of the top level Assembly?
2. There are three part numbers above the word Stage Final. Does this represent three different top level assembles or why three numbers?
3. Below "Stage 3" is one number is that the document number for a top level assembly
4. The first blue box has two numbers again. Why two numbers?
5. 824762/826230 flows into Stage 5 but I do not understand this. Stage 5 (the white box) is what I though was a subassembly of Stage Final. I would think the first green box was a sub assembly of Stage 5.
6. PBA 825514 appears to be a subassembly of Stage Final ( so are DI, IAP). But the blue box in Stage Final has three numbers. How do you interpret the other two numbers?
7. Just to be sure at this points these are all part numbers, but at a later date you would assign specific serial numbers to each part? There are no serial numbers in this view?

 

[MajP]

@Sluggo and @Pat Hartman,
After rereading I see that the issue has nothing to do with the issue of serialized or the issue of M-M. It is actually two slightly different ways of achieving the same thing. Both methods are M-M and both require 2 tables. It has to do with using a Self Referencing table or a Junction Table.

Imagine you have tblParts

TblParts
1 Part A
2 Part AA
3 Part AAA
4 Part B

And they should be related like this.
- Part A
-- Part AA
---- Part AAA
-Part B

So the parent of AA is A, and the Parent of AAA is AA.

Pat creates a junction table like this

tblBom
- BOM_ID
-PartID_FK foreign key to parts table
-ParentID_FK foreign key to parts table

where the parent_ID relates to tblParts. The data then looks like this. For demo purposes the BOM ID starts at 22

BOM ID	PartID_FK	ParentID_FK
22	1
23	2	1 (parent id of AA is A)
24	3	2 (parent ID of AAA is AA)
25	4

I create a self Refencing table like this
tblBom
- BOM_ID
-PartID_FK foreign key to parts table
-BOMParentID_FK foreign key to the BOM table

Now I use a self Referencing table where the Parent ID points back to the BOM table not to the parts table.

BOM_ID	PartID_FK	BOMParentID_FK
22	1
23	2	22
24	3	23
25	4

In truth the junction table is like a deconstructed self referencing table because when you query you bring in the parts table twice.

Both methods allow you to enforce referential integrity. The junction table may be more intuitive if you have never worked with a self referencing.

I think the big difference is if these parts are serialized. In the junction table method you would have to add all the serialized parts in tblParts. You would pick a serialized part. With the self referencing you could add the serialization in the BOM table and have only generic parts in your parts table. This is then only a single instance of a part.

tblBom
- BOM_ID
-PartID_FK foreign key to parts table
-PartSerial (serial for the specific part)
-BOMParentID_FK foreign key to the BOM table

The latter may provide a little more flexibility.

 

[Pat Hartman]

My contention is that ---- Part AAA could have more than one parent. Could you use the same tires on two different models of bikes? A self-referencing 1-table relationship cannot support that. You need the 2-table m-m to allow Part AAA to be used on Part AA as well as on Part C.

Whether parts are serialized or not is a red herring. You can still use the same model tire on two different model bikes. You just can't use the physical tire with serial #123 on more than one bike.

 

[MajP]

My contention is that ---- Part AAA could have more than one parent. Could you use the same tires on two different models of bikes? A self-referencing 1-table relationship cannot support that. You need the 2-table m-m to allow Part AAA to be used on Part AA as well as on Part C.

From what explained and showed, of course AA can be used on more than one part if it is generic. I think you need to reread what I showed. Remember the PartID_FK points to the Part table for a generic part.

Assume
- Part A
-- Part AA
---- Part AAA
-Part B
-- Part AA (used under Part B)

BOM_ID	PartID_FK	BOMID_FK
22	1
23	2	22
24	3	23
25	4
26	2	25 (Part AA child of PartB)

As I already said if you are doing it generic than it requires two tables either way. With a self referencing table (parent id points to the bom table) or junction (parent ID points to the parts table). If it is purely serialized where an item is discrete (Family Tree, Bird Breeding, Files in a directory) then this can be done in a single table because John Smith cannot have two biological Mothers regardless of what the Liberals tell you.

 

[Pat Hartman]

Sorry Maj, it sounded like you were saying the 1-table method will support multiple parents but it doesn't. Only the m-m method allows the same part number (obviously not the same physical part) to be used on multiple parents.

 

[Sluggo]

Hey CarlettoFed,

Sorry for the delay.

824761|826233|826234
Stage Final
826877

824761|826233|826234
- These are the part numbers of the final product.
- I had combined them as ~98% of the sub components are the same. (For example, think of a new car that comes with different trim levels. Such as the Volkswagon Jetta: Trendline, Comfortline, Highline. These 3x vehicles are ~98% similar.
- Only 1 of the 3 part/serial numbers listed would be recorded in the field.

Stage Final
- This is just name of the part numbers.
- Some of the names are too long to be used a table or field names, so I would use the part number instead.

826877
- This is the part number of the document that the current serial numbers are recorded in.
- This is not relevant to the database.

824762|826230

- This is the same properties as 824761|826233|826234

824771
DI
825708

Similar to above: Part Number, Part Name, Document Part Number


Edit: I have completely missed all of the page 2 responses and have only responded to Carletto here. Will try to answer more when able.

 

[Sluggo]

Here are some answers to the best of my ability.

MajP - Post#21

I do believe a better (for my people) method for inputting and manipulating data in a hierarchical structure is doable.
My coding/vba skills are not great and understand that the query/report tools of access will not work as easily as a standard structure.

MajP - Post#23

1. Is "Stage Final" the name of the top level Assembly?
- Yes, this is the top level assembly.
- I realize now that there is no field for the top level assembly serial number, but this serial number is driven by the "824762|826230" serial number.

2. There are three part numbers above the word Stage Final. Does this represent three different top level assembles or why three numbers?
- There are three different top level assembles (e.i. 3x models of equipment / part numbers) that are ~98% similar.
- I could not think of another way to represent this graphically.
- In a standard structured database, I assumed that you would enter some of the sub table fields at "n/a" if they were not applicable to the top level assembly.

3. Below "Stage 3" is one number is that the document number for a top level assembly
- I assume you are referring to "829683", this is the document number for "Stage 3" (Not relevant to the database)

4. The first blue box has two numbers again. Why two numbers?
- This is related to the 3x top level assemblies.
- At the start of the whole build you chose which model you are building.
- A 824762 will become a 824761 in the final stage, while a 826230 can become a 826233 or 826234 in the final stage.
- As you progress the build, some of the sub components change slightly depending on the model you chose.
- E.g. In Stage 2 a 824762 has 1x 809966 "MPI", while a 826230 has 2x 809966 "MPI". (Not shown correctly)

5. 824762/826230 flows into Stage 5 but I do not understand this. Stage 5 (the white box) is what I though was a subassembly of Stage Final. I would think the first green box was a sub assembly of Stage 5.
- You are correct, it is a sub assembly, I have shown this poorly.
- Both 824762 & 826230 have started at 001 and are built sequentially.
- When they reach the end, they drive the serial number for the top level assembly. (They also drive the Stage 5 serial number)
- For final products, you end up with:
- 824761 001, 002, 003, 004, etc
- 826233 001, 003, 005, etc
- 826234 002, 004, 006, etc (No overlap of 826233 & 826234)
- This also means that without more detail, the Stage 5 serial numbers would read
- 824961 001, 001, 002, 002, 003, 003, etc

6. PBA 825514 appears to be a subassembly of Stage Final ( so are DI, IAP). But the blue box in Stage Final has three numbers. How do you interpret the other two numbers?
- Again, poor diagram, I was only looking at model 824761 when drawing this up.
- I have since revised the diagram where each of the 823352|823359|825514 are listed separately in "Stage Final" and each have a breakout of their sub components much like 825514 is displayed.
- Note: Only one of these (823352|823359|825514) are present in each top level assembly at a time, the other 2x would be n/a.

7. Just to be sure at this points these are all part numbers, but at a later date you would assign specific serial numbers to each part? There are no serial numbers in this view?
- Correct, there are no serial number listed. I was trying to draw this somewhat similar to the relationship diagram in access.

Pat Hartman - Post#22

The flavor of BOM "as built" is the whole point of the thread.
- Note: I am only concerned with recording descrete (serialized) parts in this exercise. The example diagram shows ~400 parts, while the actual BOM is closer to ~2000 i believe.
We already have the BOM "as designed" handled by our drawings and CAD software.
I am unsure how the BOM "as planned" fits into our practices.

I can now see where the M-M may come into play as some sub components may have the same parent at different points in time.
We would likely want to maintain a history of parts getting replaced or swapped.



Thanks,
Joe

 

[Pat Hartman]

The flavor of BOM "as built" is the whole point of the thread.

Then a single table with a self relationship will work fine.

 

[MajP]

I modified some of my Tree View demos to better support a BOM.
1. Allows you to create multiple BOMs. The Demo shows 2
3. Allows you to create both a serialized or non-serialized BOM
4. This version assumes you build a BOM from a parts list
5. Demos add, edit, drag, drop, sort, delete.
The first 20 seconds are blurry and that is just demoing that there are 2 BOMs saved. So you can have multiple.

See if you think this is close. I believe the structure is correct so additional bells and whistles to make it user friendly can be added. I have not created a fast feature to add Serials, but that can be done easily.

If this is close I can let you give it a try and will take feed back. It is probably an 80% solution

This is based on the below diagram.

 

[MajP]

In the demo I forgot to put the subassembly DI, IAP, PBS under Stage Final. Although I selected "Add Child Nodes" I think the tree lost focus so it added them as root nodes. Notice that for DI it adds the child nodes without problem and DI is highlighted. However, you can easily drag and drop the child nodes afterwards. If you drop a node on another node you can see it becomes a child and brings the whole branch. Also the first 20 seconds are blurry for some reason.