Solved Products - Sub Assemblies - Assemblies (1 Viewer)

KitaYama

Well-known member
Local time
Tomorrow, 00:56
Joined
Jan 6, 2022
Messages
1,634
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.

2024-04-02_11-57-29.jpg

Any kind of insight will be appreciated.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:56
Joined
Feb 28, 2001
Messages
27,529
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.
 

tvanstiphout

Active member
Local time
Today, 08:56
Joined
Jan 22, 2016
Messages
289
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.
 
Last edited:

KitaYama

Well-known member
Local time
Tomorrow, 00:56
Joined
Jan 6, 2022
Messages
1,634
Thanks. I'll take a look to given links.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:56
Joined
May 7, 2009
Messages
19,249
BOM on shared onedrive:
 

Attachments

  • BoM.zip
    122.4 KB · Views: 74

KitaYama

Well-known member
Local time
Tomorrow, 00:56
Joined
Jan 6, 2022
Messages
1,634
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)
ProductPKProductNameParentPK
1Product13
2product24
3Assembly14
4Assembly25
5Assembly3

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.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:56
Joined
May 21, 2018
Messages
8,702
I have written extensively on this.


See my discussion on self referecing.
See this too.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:56
Joined
May 21, 2018
Messages
8,702
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

Immoderate Moderator
Staff member
Local time
Today, 10:56
Joined
Feb 28, 2001
Messages
27,529
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.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:56
Joined
May 21, 2018
Messages
8,702
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

Super Moderator
Staff member
Local time
Today, 16:56
Joined
Sep 12, 2006
Messages
15,758
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

Super Moderator
Staff member
Local time
Today, 16:56
Joined
Feb 19, 2013
Messages
16,751
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

Well-known member
Local time
Tomorrow, 00:56
Joined
Jan 6, 2022
Messages
1,634
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.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:56
Joined
Feb 19, 2002
Messages
43,786
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

Well-known member
Local time
Tomorrow, 00:56
Joined
Jan 6, 2022
Messages
1,634
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

Super Moderator
Staff member
Local time
Today, 11:56
Joined
Feb 19, 2002
Messages
43,786
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

AWF VIP
Local time
Today, 16:56
Joined
Jul 26, 2013
Messages
10,393
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.
 

Users who are viewing this thread

Top Bottom