Solved Products - Sub Assemblies - Assemblies (1 Viewer)

[KitaYama]

I'm working on a product/order type of database and have trouble to setup the correct structure of tables and their relationships.

Here is some rules that I have to follow:

1. At present we have 44916 products and this number will grow in future. (Product1 - product2 - Product3 - .....)
   
   
2. Tens or hundreds of these products can be assembled (Assembly1 - Assembly2 - Assembly3 - ....) and be sold as one product.
   For example Assembly1 = 3 pieces of Product1 + 10 pieces of Product2 + 50 pieces of Product10 + .......
   
   
3. Each Assembly can be used as a sub assembly in a higher level of assembly. There is no limit to this. (It may go down to unlimited layers)
   For example
   Assembly5 = 10 pieces of Product100 + 20 pieces of Product50 + 3 pieces of assembly1 (which in turn contains another sub assembly)+ .......
   
   
4. The most important rule that I have to mind is that for each order, if we receive an order for an assembly that has different sub assemblies, a list of all necessary products should be saved in OrderDetails table.
   (including the necessary parts used in sub assemblies and sub sub assemblies and......)
   It means that an order can be a single order, or an assembly that contains sub assemblies and it may contain more than a thousand products for that single order.

I was thinking of something like this, but the problem is Assemblies are products and we can have orders for assembly. I can not divide the products in two tables.

Any kind of insight will be appreciated.

 

[The_Doc_Man]

Make all products (including stand-alone items) assemblies of 1 or more objects. Then, obviously, there will be only one component for the items that are sold as non-assemblies. This seems like you will have the problem of needing a self-referential junction table. The subject of "assemblies" has been asked before many times, so you might be able to search the forums for "assembly" or perhaps for "sub-assembly" and other variations of punctuation.

 

[MajP]

See solution for proper structure of assemblies.
Post in thread 'Looking for some direction.' https://www.access-programmers.co.uk/forums/threads/looking-for-some-direction.327067/post-1870694

 

[tvanstiphout]

This is indeed a common question, so you should not have any trouble finding web pages discussing it.
One thing I would add: since you will be using self-referencing tables, recursing them is MUCH easier with the excellent support for that in SQL Server, as opposed to the non-support in Access/ACE. This should be a strong reason to select that back-end for your project.
See also: this and that.

 

[KitaYama]

Thanks. I'll take a look to given links.

 

[arnelgp]

BOM on shared onedrive:

 

[KitaYama]

I took a look at the given links, but before stepping in the ring, I thought I need to learn more about self referencing tables.
If I understand the self referencing tables concept correctly, a self referencing table, has a field that contains the PK of its parent.

• ProductPK (primary key, uniqueidentifier, not nullable)
• parentPK (foreign key, uniqueidentifier, nullable)

ProductPK	ProductName	ParentPK
1	Product1	3
2	product2	4
3	Assembly1	4
4	Assembly2	5
5	Assembly3

Well, my problem is any product may have been used in several hundred assemblies.
In above simple structure, Product1 is used in Assembly1. What if I need to add it to Assembly3 too? (Actually it may be used in all assemblies)
Or how the table looks if Assembly2 is used in both Assembly1 and Assembly3?

thanks.

Edit:
@arnelgp I didn't see your post while typing. I'll take a look.

 

[KitaYama]

BOM on shared onedrive:

Never thought of a many to many relation to the same table.
Seems it can work.
Let me do some tests.

Thanks again.

 

[MajP]

I have written extensively on this.

Hierarchical Data, Self Referencing Tables, and Recursion Part 2

This is part two to this thread. https://www.access-programmers.co.uk/forums/threads/hierarchical-data-recursion-tree-views-and-a-custom-class-to-assist.309753/ However the original thread dealt more specifically with tree views. This does not. The above threads has many links to other treeview...

www.access-programmers.co.uk

See my discussion on self referecing.
See this too.

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

I have had some people ask questions on working with Hierarchical data and Tree Views, which often involves using recursion. If not familiar with recursion this is where a procedure repeatedly calls itself until hitting a condition where it is told to stop. This is used often where you have to...

www.access-programmers.co.uk

 

[MajP]

Also you really need to look at my demo that I linked. It is a complete working solution, and you probably just need to load your parts into it and start making Assemblies (BOMs)

Well, my problem is any product may have been used in several hundred assemblies.
In above simple structure, Product1 is used in Assembly1. What if I need to add it to Assembly3 too? (Actually it may be used in all assemblies)
Or how the table looks if Assembly2 is used in both Assembly1 and Assembly3?

The structure should show how you have a generic "pool" of equipment and can create multiple different BOMs from that.

 

[The_Doc_Man]

While I have not written as extensively as MajP, I used the self-referencing table in my genealogy database. If you treat it like a many/many relationship, it can handle ALL SORTS of issues, and in my case can handle families that go back eight to ten generations depending on how far I could get when doing the research. Surely you don't have greater than depth of 8 layers. But even if you do, it won't matter. The limit is based on the size and number of component records, and that 2 Gb limit doesn't approach terribly fast if you limit the size of the (self-)junction table records.

 

[KitaYama]

Surely you don't have greater than depth of 8 layers.

I do.

But even if you do, it won't matter.

That's a relief.
I'll check all suggested methods as soon as I'm back to my desk.

Thanks to all.

 

[MajP]

As pointed out by @tvanstiphout, Access has no native capability to query or work with hierarchical data. If your backend is SQL Server you can do a lot more. This means in Access you will rely on VBA and often recursive code. Usually this means creating temp tables to store your "query" like results.
Imagine each of your parts has a cost and you want the cost of each Assembly (made up of nested sub assemblies). There is no way to do that in Access SQL. You have to do that in code. However, you can do that in SQL server with recursive CTE. I have not done much in SQL Server with hierarchical, but I found it pretty challenging.

 

[gemma-the-husky]

This must be a question of correct data structure, and I'm pretty sure the examples offered will deal with it correctly.

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. Managing blended families is maybe not true genealogy, and brings in lots of complications.

 

[CJ_London]

I’ve found using a vba recursive function to populate a disconnected ado recordset and assigning the recordset to a form works well - providing there are not too many records being analysed - perhaps a thousand or two, which would be good practice anyway to minimise network traffic

only issue with ado is you cannot assign it to a report recordset

 

[KitaYama]

As pointed out by @tvanstiphout, Access has no native capability to query or work with hierarchical data. If your backend is SQL Server you can do a lot more. This means in Access you will rely on VBA and often recursive code. Usually this means creating temp tables to store your "query" like results.
Imagine each of your parts has a cost and you want the cost of each Assembly (made up of nested sub assemblies). There is no way to do that in Access SQL. You have to do that in code. However, you can do that in SQL server with recursive CTE. I have not done much in SQL Server with hierarchical, but I found it pretty challenging.

Yes, the backend will be sql server. We are not allowed to keep any kind of data local to our PCs.
I will read about suggested CTE, but I think I need a correctly structured table design first. That's why I'm going with given solutions here first.

Most of those who replied to this thread see my situation as Hierarchical, but if my take on "Hierarchical" is correct, a production system is not like an organization, in which people are organized into different levels of importance from highest to lowest in different branches. But I may be wrong. Before judging, I need a little more time to read and understand all given resources in depth.
I'm reading your two articles linked in #9 now and am trying to figure out what will happen if an agent has to report to 3 persons (Bethany, Charlie & Allen). Or what if James has to report to Ingrid, Allen, Debbie & Charlie.

If I couldn't figure it out, I will come back and beg for more help.
Maybe the generic pool you talked about in #10, is the answer I'm looking for. I'll try to check your sample database in #10 during weekend. It's hard to learn something new while doing my job nowadays.

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. Managing blended families is maybe not true genealogy, and brings in lots of complications.

Yes, when you step into a manufacturing system, it's a completely different world. It's even more confusing than your Genealogy database when it has to care for Biological, Foster & Step parents and siblings too.
Wish me luck.


Thanks again.

 

[Pat Hartman]

Surely you don't have greater than depth of 8 layers.

The BOM for a 747 only went 8 layers although we allowed for 16. Sure surprised me.

Separate the concept of the BOM from inventory. Do you keep assemblies in inventory or is everything custom built when the order comes in?

In the product table, everything is marked as an End Item, an Assembly, or a Detail Part. In your case, Assemblies can be sold as well as End Items. Perhaps you even sell Detail Parts.

The BOM application I build for Boeing was to manage As Designed, As Planned, and as Built configurations.

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. Genealogical applications track physical parentage. You can only have ONE birth mother and ONE sperm donor so the Ancestry application uses a self referencing table but this is a many-many relationship but it uses the part table twice. Once to get the assembly and on the other side for the Detail Part or sub assembly.

 

[KitaYama]

Do you keep assemblies in inventory or is everything custom built when the order comes in?

No, they are built-to-order.

Perhaps you even sell Detail Parts.

Yes, we do.

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.

So far, me too. As I explained above, I don’t see how a simple product, may be used in a hundred assemblies. But I’m not yet finished with offered resources. So far @arnelgp ’s junction table with many to many relation to product table seems promising.

 

[Pat Hartman]

You have a m-m. It is just an unusual m-m since most of the time the left and right tables are different. In this case, the part table is on both sides of the relationship. That is probably why Doc thought it was the same as his Ancestry relationship.

By using the End Item, Assembly, and Detail flags, you can control which type of part is left and which is right. The only valid combinations are:
EI-A
EI-D
A-A
A-D
The only other edit would be to ensure that Ab/Ac and Ac/Ab cannot both be valid.

 

[Minty]

I dealt with assemblies in my last job (a few years ago now).
Mainly IT equipment, so Servers, controllers, disk enclosures, disks etc.
All were individual products but some could contain other products.
I'll have to dig out how we structured it, but from memory we had nothing that went more than about 4 or 5 deep. I'm pretty sure it was all handled in SQL with CTE's, and a very troublesome Treeview control to view it if necessary.