Solved Need some advice for the design of Products table

[KitaYama]

@CJ_London

Is this structure what you have in mind?

And this is some data:

If I may, I have a question:
We have two orders. OrderPK 1 is an order for AP1234YA123. Is the order details correct for this order?

Thanks again for all your help.

 

[ebs17]

To allocate the products and components to each other, refer to the product table with itself. You can use this to create as many hierarchical levels as you like.

 

[KitaYama]

To allocate the products and components to each other, refer to the product table with itself. You can use this to create as many hierarchical levels as you like.

I really appreciate the advice. I'll do some testing and will be back.

 

[CJ_London]

Is this structure what you have in mind?

I think of this the other way visually so I would do your joins slightly differently

sales? orders>orderlines>products>assemblies>products_1

Products would link to assemblies on productPK to kitFK with a left join (since not all products have an assembly)
the _1 is the same products table, just aliased to appear a second time

to determine the full list of products for an order you can use the nz function

nz(product_1.productPK, productPK)

Just modified your database
The relationships now look like this (I've changed the name of KitFK)

because of the left join, you need an aliased query, kept separately here

qryAssemblyProducts

SELECT *
FROM tblProducts INNER JOIN tblAssemblies ON tblProducts.ProductPK = tblAssemblies.ProductFK;

To list all products required for an order you then have this query
qryRequired

SELECT tblOrders.OrderPK, tblOrders.CustomerPK, tblProducts.ProductPK, tblProducts.Product, tblOrderDetails.OrderDetail_ProductCount, qryAssemblyProducts.UsedCount,
">>>" AS spacer, Nz([qryAssemblyProducts].[ProductPK],[tblProducts].[productPK]) AS ReqProductPK, Nz([qryAssemblyProducts].[Product],[tblProducts].[product]) AS ReqProduct, Nz([qryAssemblyProducts].[usedcount],1)*[OrderDetail_ProductCount] AS ReqQty

FROM (tblProducts INNER JOIN (tblOrders INNER JOIN tblOrderDetails ON tblOrders.OrderPK = tblOrderDetails.OrderFK) ON tblProducts.ProductPK = tblOrderDetails.OrderDetail_ProductFK) LEFT JOIN qryAssemblyProducts ON tblProducts.ProductPK = qryAssemblyProducts.ParentProductFK;

The spacer column is just to separate data from the calculations

I added an assembly item to your order 2 to demonstrate and attached your modified db

 

[KitaYama]

sales? orders>orderlines>products>assemblies>products_1

By orderlines do you mean what I have as tblOrderDetails?
For now I don't have any intention to add sales before I can understand the products assemblies and orders' tables structure.

thank you.

 

[gemma-the-husky]

I really don't understand what you're trying to tell me. The situation is what I explained in my first post. Just as you can go and buy a whole car, or you can go and buy a bumper or a door after you have an accident. It means the manufacturer not only sells a whole assembled car, they also sell the parts.
If it's not what you mean, please rephrase it somehow I can understand what you mean.


1- We don't have any option for our products. So all above situation is irrelevant to our situation. We're not selling furniture or something of the sort to have any option, color etc. We are talking about industry and there's not two color of the same thing or two type of the same thing. At least for us it's the way it is. We have thousands of products, each one has its purpose until a new version of the same part is designed. So (for us) there's no situation where customerA wants it with golden bracket but customerB wants it in silver.
We have drawings, and very strict manuals to follow for each product. So there won't be a "Make it easy for the staff to pick the items" case.

Let me give you a clearer perspective. (I will delete the following images as soon as I'm sure you've seen them. They will fire me if anybody sees them on a forum)
View attachment 104862

View attachment 104863

These are two products. We receive an order for any of them, or at some point any single part within them. As you see in the right table, there's a productNo associated with each part used in this product (assembly). We receive an order for the whole thing or sometimes any of single products used in it (listed at left).

2- Prices and selling will not be managed in this database. The only thing I care is to have a normalized table to manage the incoming orders.

I won't try to break down your post which I have quoted.

Here are some general musings

Even though you don't put finished goods into stock, I am sure you don't sell finished stock as multiple parts. Unless you make these products in a single build, I expect you make subassemblies and then incorporate subassemblies in a build. Maybe a particular subassembly can be included in more than one product. A certain motor say be built into more than one product. Do you really not have a stock of ready built and tested motors, or anything else to build into a final product. Maybe not, but it still doesn't invalidate it as a logical analysis.

So if you have a product X that is composed of a complex arrangement of parts, it still might be better to notionally consider the whole consisting of parts and subassemblies, themselves composed of parts, that you notionally build into a finished product. eg In the breakdown below item 1 is used in the final build plus in the construction of some of the sub-assemblies. Once you allocate a part to be built into product X presumable you need to allocate it in your stock system so that another manufacturing request cannot take your part. Maybe you pick every part, put it on a large tray, and assemble it there and then. Maybe you make some of the sub-assemblies first. Even if you don't actually put stock the finished products into stock, it still might logically make sense to put the finished item and the sub-assemblies into notional stock, so you can sell a finished product rather than sell many different parts. Maybe you decide to make some common sub-assemblies and hold them ready assembled to save time. Even if you don't do that now, you decide to do it in the future.

the diagrams we never saw look like they have sort of sub assembly units. @CJ_London just noted there might be screws, fuses, and so on that are common to multiple products. Even if they aren't they might at some time, and it would make sense to build a system that works in that eentuality.

It would surely be easier to have your production system turn 1000 parts into a single part X, put part X into notional stock, and then sell it as a finished product. That might make your management system easier.

Product X

Item 1 x 2
Item 13 x 1
Item 28 x 6
Item 37 x 4
Item 65 x 9

assembly 4 x 2
Item 1 x 12
Item 2 x 6
Item 8 x 14
Item 24 x 16
Item 132 x 3

assembly 12 x 1
Item 1 x 6
Item 3 x 6
Item 19 x 14
Item 17 x 16
Item 48 x 3

 

[KitaYama]

I won't try to break down your post which I have quoted.

Here are some general musings

Even though you don't put finished goods into stock, I am sure you don't sell finished stock as multiple parts. Unless you make these products in a single build, I expect you make subassemblies and then incorporate subassemblies in a build. Maybe a particular subassembly can be included in more than one product. A certain motor say be built into more than one product. Do you really not have a stock of ready built and tested motors, or anything else to build into a final product. Maybe not, but it still doesn't invalidate it as a logical analysis.

So if you have a product X that is composed of a complex arrangement of parts, it still might be better to notionally consider the whole consisting of parts and subassemblies, themselves composed of parts, that you notionally build into a finished product. eg In the breakdown below item 1 is used in the final build plus in the construction of some of the sub-assemblies. Once you allocate a part to be built into product X presumable you need to allocate it in your stock system so that another manufacturing request cannot take your part. Maybe you pick every part, put it on a large tray, and assemble it there and then. Maybe you make some of the sub-assemblies first. Even if you don't actually put stock the finished products into stock, it still might logically make sense to put the finished item and the sub-assemblies into notional stock, so you can sell a finished product rather than sell many different parts. Maybe you decide to make some common sub-assemblies and hold them ready assembled to save time. Even if you don't do that now, you decide to do it in the future.

It would surely be easier to have your production system turn 1000 parts into a single part X, put part X into notional stock, and then sell it as a finished product. That might make your management system easier.

Product X

Item 1 x 2
Item 13 x 1
Item 28 x 6
Item 37 x 4
Item 65 x 9

assembly 4 x 2
Item 1 x 12
Item 2 x 6
Item 8 x 14
Item 24 x 16
Item 132 x 3

assembly 12 x 1
Item 1 x 6
Item 3 x 6
Item 19 x 14
Item 17 x 16
Item 48 x 3

@gemma-the-husky Thanks for your interest and lengthy explained post. I have to read it several time to be sure I've digested what I'm told. I'll reply then.

thanks again.

 

[arnelgp]

You can download the db here (BOM).
Not sure how to make this relationship - 3 level BOM - Microsoft Community

 

[KitaYama]

@arnelgp
Million thanks. I'm in the middle of a very important process. As soon as I'm finished I'll download and see how it's done.

I really appreciate your help.

 

[KitaYama]

I think of this the other way visually so I would do your joins slightly differently

sales? orders>orderlines>products>assemblies>products_1

Products would link to assemblies on productPK to kitFK with a left join (since not all products have an assembly)
the _1 is the same products table, just aliased to appear a second time

to determine the full list of products for an order you can use the nz function

nz(product_1.productPK, productPK)

Just modified your database
The relationships now look like this (I've changed the name of KitFK)
View attachment 104866

because of the left join, you need an aliased query, kept separately here

qryAssemblyProducts

SELECT *
FROM tblProducts INNER JOIN tblAssemblies ON tblProducts.ProductPK = tblAssemblies.ProductFK;

To list all products required for an order you then have this query
qryRequired

SELECT tblOrders.OrderPK, tblOrders.CustomerPK, tblProducts.ProductPK, tblProducts.Product, tblOrderDetails.OrderDetail_ProductCount, qryAssemblyProducts.UsedCount,
">>>" AS spacer, Nz([qryAssemblyProducts].[ProductPK],[tblProducts].[productPK]) AS ReqProductPK, Nz([qryAssemblyProducts].[Product],[tblProducts].[product]) AS ReqProduct, Nz([qryAssemblyProducts].[usedcount],1)*[OrderDetail_ProductCount] AS ReqQty

FROM (tblProducts INNER JOIN (tblOrders INNER JOIN tblOrderDetails ON tblOrders.OrderPK = tblOrderDetails.OrderFK) ON tblProducts.ProductPK = tblOrderDetails.OrderDetail_ProductFK) LEFT JOIN qryAssemblyProducts ON tblProducts.ProductPK = qryAssemblyProducts.ParentProductFK;

The spacer column is just to separate data from the calculations

I added an assembly item to your order 2 to demonstrate and attached your modified db

@CJ_London Unfortunately I'm still in a Zoom meeting and even though I downloaded your demo, I've not been able to test it.
But seeing the relationships and reading your description, I'm sure it's perfect for what we have in mind. I think I couldn't come to this result in a hundred years if I was on my own.

still 2 simple questions and I promise I won't bother you more.
1- It's the same question as in #21. I just want to be sure it won't cause troubles. When we have an order of a single (not assembled) product , there's a record for it in tblOrders and a record in tblDetailsOrder. Both records have the same ProductFK. Is it OK?

2- When we receive an order for a product that contains 50 or 60 parts (an assembly or a kit), after adding a record in tblOrder, how exactly the members should be added to tblDetailsOrders. Should I run a procedure to create a recordset of its child products and run an INSERT query to add them one by one?
Or is there some other more convenient way?

Thanks again for your time and help.

 

[CJ_London]

1. tblOrders should not hold the productFK, just header information - ordernum, customerFK, orderdate, etc , product FK is stored in the orderdetails table together with OrderFK and quantity- see my example. You need to remove the orderproductfk field from tblOrders.

2. not sure I understand, - you add to orderdetails, not order for the order is for an assembly, they just enter the partnumber for that assembly. The app knows from the assemblies table what parts go into making the assembly - again as demonstrated in the db I sent

 

[KitaYama]

1. tblOrders should not hold the productFK, just header information - ordernum, customerFK, orderdate, etc , product FK is stored in the orderdetails table together with OrderFK and quantity- see my example. You need to remove the orderproductfk field from tblOrders.

2. not sure I understand, - you add to orderdetails, not order for the order is for an assembly, they just enter the partnumber for that assembly. The app knows from the assemblies table what parts go into making the assembly - again as demonstrated in the db I sent

Got it.

I think all my questions are answered and I have the solution I was looking for.
Many many thanks.

 

[Pat Hartman]

The Product table contains every part you sell either individually or pre-assembled. The assembly (or kit) table breaks down all the parts (which may themselves be assemblies which is why someone recommended a BOM). Inventory is kept at the part level. If the components are already assembled, they are not counted in the inventory for that part since you wouldn't be very likely to break up an assembly to sell part of it. If you do, that is a different problem. You would subtract 1 from inventory for the assembly and add 1 for each component, and then sell the individual component.

I'm confused about why you would be breaking down the assemblies in an order entry system. If the assemblies are not stocked that way, you could certainly use the assemblies table to add a line item for each individual part but is the customer expecting to get something assembled or in pieces? He's going to be really disappointed if he thinks he ordered an assembly and he gets a box of parts.

 

[gemma-the-husky]

I agree Pat.

I don't know if you remember, but @KitaYama has a really complicated and sophisticated system of managing product traceability - I am sure you contributed to that discussion - and I was surprised that it didn't already manage inventory in order to inform the traceability.

 

[Pat Hartman]

Tracking where a product is used is different from the order process. You always track where items are used at the detail level if you need to backtrack to find other items from the same heat, batch, or lot, so if that is what we are talking about, that is a different problem from ordering something.

 

[KitaYama]

and I was surprised that it didn't already manage inventory in order to inform the traceability.

@gemma-the-husky
As I explained in PM, that was for line 2 & 3 (machining) and the system you're talking about is under the last stage of testing. It will be on-line from 2023/01/01

This question was for Line 4 (Sheetmetal). And as I explained at present this production line doesn't need an inventory.

I'm confused about why you would be breaking down the assemblies in an order entry system.

@Pat Hartman I'm sure how many times I try to explain, it would be hard to understand it. let me give you an example.
An average assembly has 50 to 400 components. I take the following assembly as an example. It has 352 parts. Get ride of the ready made components (screws, hinge, etc). 311 components are left. All of these 311 components are our products.

We have received an order for this assembly. (3200 Pcs)
From the point of ordering, it's only one order with the quantity of 3200. I understand it. And as you expect there should be only one record for this order in order table. You are correct up to this point.

But from the point of production, we have to manufacture 312 component. For us, it's not one order. We have to manufacture 312 products. And We need to be able to trace the production processes, who has done what, which machine and operator? is it finished? if not at what stage of production it is? when will it available for being assembled? etc.

Sometimes the customer calls us and asks if it is possible to do some changes to one of those 312 components. We can not leave our desks and spread in the whole company to find out the progress of that specific component. OR where it is? who is in charge of its production? etc. Every piece of data about that assembly and it's components should be available for us at any moment.

To be able to have this information, we have an Order table and a DetailedOder table. Even if it's one order, we save all the components in the detailed table. Then there's a processes table that has a FK to to detail table's PK. (one to many relation). The processes of each those 312 components are saved in the process table.

This is the search result of one of those component.

This is the situation of one of those 312 components.
The left side is the single form of tblOrdersDetail. The right side is a continuous sub form linked to process table.
The blue rectangle is the assembly we have to produce. (It's shown here only with dLookup from tblOrders).

At the left side, you can see the process and who and when it's been done. The empty textboxes shows that process has not been processed yet.
the filled one is the name of the operator and the date. A double click on any of them, brings up a more detailed form about each process, machine, (for security reasons I can not show it here)
There's also the name of who is in charge of this part's production. We can send a message to him through this form and ask him to stop/continue the production or ask for any kind of change.


Again to be able to have this data, even if the order is one order, for us it means manufacturing 312 components. And this is only one order. There are more orders. And if we don't manage it this way, who can take care of the mess?

If we don't have a reliable system to check these info, we have to go to shop and search for each one of those component and I don't need to explain how hard it would be.

You may not believe it. But this form feeds the production machines too. This database creates and sends all the NC data for CNC machines. The operator searches for the component he want to work on and click a button. All the necessary NC data is sent to his machine and he only presses the start button.

I hope I'm clear enough.

You have a better idea? I'm all ears.
Thank you for your interest and trying to help

 

[Pat Hartman]

We have to manufacture 312 products. And We need to be able to trace the production processes, who has done what, which machine and operator? is it finished? if not at what stage of production it is? when will it available for being assembled? etc.

As I said, I understand why you might want to break down the manufacturing process but you were asking about the Order entry process and the tables that support that. A BOM is a recursive process. you can simplify it if you know the maximum depth of your BOM. I was amazed by how shallow the BOM was for a Boeing 747 given its complexity. I think it was only 8 levels deep. That is because much of the plane was built from assemblies that were not exploded since we were working with final assembly. So you would have 4 wheel assemblies but you would never see the components of that assembly. A lot of our process was built around what they called "low level code". That was a level indicator that was used to identify the lowest level at which any given part occured in any assembly.

You can hard code a query to go 8 levels (I would go at least two deeper than you need) or you can create a recursive VBA loop to go an infinite number of levels. The end result is a single table that contains all the individual parts from the explosion summarized so instead of having part a occur 3 separate times, you would have one row with a quantity of 3 with the low level code for the lowest level. You build from the bottom up which is why the low level code is important. You need to build the lowest level parts first but you don't want to set up/break down multiple times so you build all the three parts at the lowest level where one is needed. This table feeds the manufacturing process. There would then be tables below that one that track the steps of the process. There would be different kinds of steps depending on the actual part being built. I would need to know far more about the actual manufacturing process to even guess at what you need.

 

[KitaYama]

I saw you mentioning BOM earlier. The only BOM I'm familiar with is in CAD system. I have it in my list of surveys and studies.

Thanks again

 

[Pat Hartman]

Bill of Material. You may already be able to find an exploded version (ie all assemblies expanded) in your manufacturing system. This is not something you should need to build yourself. See if your users or IT people can tell you where it is located.

 

[KitaYama]

I'm the one who build them. As I said we have it in our CAD and on all our drawings.
I'm trying to understand (study) how to use them in our database.