Solved Need some advice for the design of Products table

[KitaYama]

We have Product1, Product2, Product3, Product4.....
Product1 and product2 are single, standalone products, but product4 is an assembly of Product1, Product3 & Product120

Sometimes we receive orders for Product1 or product2.
Sometimes we receive orders for Product4.

Just as an example, you can imagine sometimes we receive orders for chain or break or light, and sometimes we receive orders for a whole motorcycle.

I'm stuck to the design of the Products and Orders tables.
Even if we receive order for an assembled product (a whole bike), we should have a detail order table (with all necessary products) to be able to track the manufacturing process.

I really appreciate if you can show me a starting point for this situation.

Edit: For now all my concern is products and orders tables. We can managing the processes tables.
Thanks again.

 

[Pat Hartman]

Kits, which is what this is referred to in large manufacturing systems are difficult to handle. You have to have all the components plus the kit in inventory. The quantiles should reflect what you have. ie. you don't count any quantity for a kit unless it is stocked in its assembled form. Then you need a table where you define kits. That lets you know if you have the material to build a kit in inventory. From there it gets complicated. So, it depends on your order entry process and how you would communicate the need to build a "kit" and put it in to inventory so you could sell it.

 

[KitaYama]

@Pat Hartman Thanks for the advice.
This question is different with my previous questions about inventory.
This section has no inventory. It will be a BTO (Build-to-order manufacturing system. Or Production-On-Order).

So for now I don't need to think of the inventory section. The manufacturing line starts after the orders come in and manufactures exactly as the amount of order.

Thanks again for your help.

 

[Pat Hartman]

Ok, then start by adding the kit table that defines the composition of the assemblies.

 

[KitaYama]

Ok, then start by adding the kit table that defines the composition of the assemblies.

If I had a solution I wouldn't ask.
In above example, Product4 is an assembled product. Should it be in tblProducts too? Or it goes only in tblKits?
How exactly tblKits looks like? Is tblKits a child table to tblProducts (has a one to many relation to tblProducts)?

Thank you.

 

[Solo712]

If I had a solution I wouldn't ask.
In above example, Product4 is an assembled product. Should it be in tblProducts too? Or it goes only in tblKits?
How exactly tblKits looks like? Is tblKits a child table to tblProducts (has a one to many relation to tblProducts)?

Thank you.

Am I the only one here who thinks this all has no relation to real life and the problem is purely a figment of someone who has never been a mile from a real manufacturing process and posts here riddles for people eager to flash their expertise? Maybe I am.

Jiri

 

[Pat Hartman]

If I had a solution I wouldn't ask.

And if I had all the specs I might be able to design one

In my original response, I said that the kits, go in the product table as do the components. I also said you needed a table to define the components in a kit. If you want a bike, you order a bike. If you don't have one in inventory (which for some reason you are separating from this process), then whatever process gets the build order, uses the kit junction table to figure out what the components are if there is no bike to pull to fulfill the order.

Aren't you running a Master Scheduler to control production?

 

[gemma-the-husky]

We have Product1, Product2, Product3, Product4.....
Product1 and product2 are single, standalone products, but product4 is an assembly of Product1, Product3 & Product120

Sometimes we receive orders for Product1 or product2.
Sometimes we receive orders for Product4.

Just as an example, you can imagine sometimes we receive orders for chain or break or light, and sometimes we receive orders for a whole motorcycle.

I'm stuck to the design of the Products and Orders tables.
Even if we receive order for an assembled product (a whole bike), we should have a detail order table (with all necessary products) to be able to track the manufacturing process.

I really appreciate if you can show me a starting point for this situation.

Edit: For now all my concern is products and orders tables. We can managing the processes tables.
Thanks again.

I am not sure about the analogy comparing a manufactured bicycle with its components. You surely wouldn't need to know the particular gear train that is in the manufactured unit. You would know it, but you wouldn't need to know it for inventory purposes. You surely wouldn't disassemble a manufactured product to use a part of it. Mind you, you have described some complicated things before, but I still don't think you would count parts already incorporated in fully manufactured product as raw inventory.

I see a kit (parts explosion) as more like a bathroom stockist where you sell a bathroom set consisting of a sink and pedestal stand, maybe a toilet base and cistern, and then maybe chrome handles and taps or gold handles and taps. You might also have a bath, with a left panel, right panel, end panel etc, depending on how it's to be installed. There will be lots of tap alternatives for any bathroom suite.

So you might have different packs for "chrome sink", "gold sink", chrome toilet", "gold toilet", "chrome full set", and "gold full set". You can offer a discount on a "pack" compared with the prices for separate items. When you make a sale of one of the packs, it would most likely process the separate components contained in that set. Then if you have a partial "stockout" (no sink pedestals for instance) you need to decide whether to place a back order, refuse the order, offer alternatives and so on.

Even if you don't price the "pack", you want to make it easy for the staff to pick all the items they need to form the full kit. So they pick one "chrome bath kit, left-handed bath", and it identifies all the 12 parts that need to be picked. A kit with a right-handed bath has a different side panel.

Furniture stockists sell chairs and sofas separately or as sets, with different size sofas. Maybe they have special prices for certain configurations.

@Solo712 Jiri, No I don't think this is without a real application. Kits are used in real businesses as above, depending on the industry.

 

[gemma-the-husky]

If I had a solution I wouldn't ask.
In above example, Product4 is an assembled product. Should it be in tblProducts too? Or it goes only in tblKits?
How exactly tblKits looks like? Is tblKits a child table to tblProducts (has a one to many relation to tblProducts)?

Thank you.

I think a kit item, would be a product in the product table, but linked to a kit table, that holds the parts explosion.
I would think you would store and count the parts individually, and the kits are just there for stores and sales purposes. It's easier to keep the kit in the same table, otherwise you end up duplicating a lot of code.

But you mention "assembled". I think once you make an engine out of engine parts, you now just have one engine in stock, and not the constituent parts. (my personal view). As I noted you've mentioned before that you have very rigorous product tracking, so you may need a different paradigm.

 

[CJ_London]

A a starting point I would have a table of all products/kits etc and a separate child table to list products to make a kit. Basically a many to many table relating back to the parent table

tblProducts
productPK...Name
1......................Product1
2......................Product2
3......................Product3
4......................Kit1


tblAssemblies
AssemblyPK...KitFK...productFK
1.........................4...........1
2.........................4...........2
3.........................4...........3

As others have said, it is not always straightforward - can a kit have components which are other kits? Are products specific to a kit? Do kits have options for different products? Are some products interchangeable? etc

 

[Solo712]

@Solo712 Kits clearly have real life applications, as above, depending on the industry.

Dave, my comments were not concerning "kits" or "kitting" but the level of abstraction in the OP which makes me strongly suspect that this is a fictitious exercise. I have worked in my time on more than a dozen order management/manufacturing production control/inventory management systems as varied as Heating/AC, Specialty Meats, Utility Structures, Chandeliers, Textile Dyeing, Auto Parts. I simply refuse to believe that anyone in real world would be so nebulous about his/her environment and tasks at hand as the author of the OP.

Best,
Jiri

 

[Solo712]

I think a kit item, would be a product in the product table, but linked to a kit table, that holds the parts explosion.

The suggestion of a "kit" table came from Pat. But the OP only speaks of "Products", not of "components", "subassemblies", "support structures" or "knick-knacks". Then, no-one has yet asked if the "kits" would be "external" i.e. sold as separate SKUs or internal bundling of components to simplify inventory.

Best,
Jiri

 

[KitaYama]

I am not sure about the analogy comparing a manufactured bicycle with its components. You surely wouldn't need to know the particular gear train that is in the manufactured unit. You would know it, but you wouldn't need to know it for inventory purposes. You surely wouldn't disassemble a manufactured product to use a part of it. Mind you, you have described some complicated things before, but I still don't think you would count parts already incorporated in fully manufactured product as raw inventory.

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.

I see a kit (parts explosion) as more like a bathroom stockist where you sell a bathroom set consisting of a sink and pedestal stand, maybe a toilet base and cistern, and then maybe chrome handles and taps or gold handles and taps. You might also have a bath, with a left panel, right panel, end panel etc, depending on how it's to be installed. There will be lots of tap alternatives for any bathroom suite.

So you might have different packs for "chrome sink", "gold sink", chrome toilet", "gold toilet", "chrome full set", and "gold full set". You can offer a discount on a "pack" compared with the prices for separate items. When you make a sale of one of the packs, it would most likely process the separate components contained in that set. Then if you have a partial "stockout" (no sink pedestals for instance) you need to decide whether to place a back order, refuse the order, offer alternatives and so on.

Even if you don't price the "pack", you want to make it easy for the staff to pick all the items they need to form the full kit. So they pick one "chrome bath kit, left-handed bath", and it identifies all the 12 parts that need to be picked. A kit with a right-handed bath has a different side panel.

Furniture stockists sell chairs and sofas separately or as sets, with different size sofas. Maybe they have special prices for certain configurations.

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)




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.

 

[KitaYama]

Am I the only one here who thinks this all has no relation to real life and the problem is purely a figment of someone who has never been a mile from a real manufacturing process and posts here riddles for people eager to flash their expertise? Maybe I am.

Jiri

@Solo712
I'm not sure you are the only one who thinks I'm lying and posting riddles to amuse myself or not. Maybe there are more.
But I can assure you that you are free to think whatever you want about me. It's a free world after all.
And I don't feel any obligation to defend myself.

 

[KitaYama]

But you mention "assembled". I think once you make an engine out of engine parts, you now just have one engine in stock, and not the constituent parts. (my personal view).

Let me break it this way. Once a car manufacturer makes an engine out of engine parts, they have now one engine. You are correct.
1- Again I don't send it stock. As I explained it's BTO (Build-to-order manufacturing system. Or Production-On-Order). Now a days, it's very rare (at least in my area of job) to put something in stock. Because it may not be sold and it's a loss. No company takes that risk any more. We receive order, then we manufacture what is ordered.

2- In your engine example, even if the engine is sold as one item, at some point, the assembled parts are (can be) manufactured to be sold in case of troubles, accidents, break downs.

As I noted you've mentioned before that you have very rigorous product tracking, so you may need a different paradigm.

Yes I did and we have.
It's for another section, completely independent with the previous situation. Hence a new theread. That case was solved. Thanks to you and all others who helped out.

 

[KitaYama]

A a starting point I would have a table of all products/kits etc and a separate child table to list products to make a kit. Basically a many to many table relating back to the parent table

tblProducts
productPK...Name
1......................Product1
2......................Product2
3......................Product3
4......................Kit1


tblAssemblies
AssemblyPK...KitFK...productFK
1.........................4...........1
2.........................4...........2
3.........................4...........3

As others have said, it is not always straightforward - can a kit have components which are other kits? Are products specific to a kit? Do kits have options for different products? Are some products interchangeable? etc

At last some actual thing that I can work on.
Thanks @CJ_London
I'll work on it and will be back for more questions.

I really appreciate it.

 

[KitaYama]

Dave, my comments were not concerning "kits" or "kitting" but the level of abstraction in the OP which makes me strongly suspect that this is a fictitious exercise.

@Solo712
From the very first day I joined, I was suspected to be not me, but someone else. I was thought to fake my identity and now suspected to be lying and playing games.
You and others may be right. As I said above, I don't feel any obligation to defend myself.
It's your choice to believe what you read or not.

To be true, I don't even care. I see it's the way AWF is and I'm getting used to it. As long as I see very friendly members and I can learn a lot from them, I can ignore the bad feeling that every now and then AWF gives me.
Nothing's perfect.

 

[KitaYama]

The suggestion of a "kit" table came from Pat. But the OP only speaks of "Products", not of "components", "subassemblies", "support structures" or "knick-knacks". Then, no-one has yet asked if the "kits" would be "external" i.e. sold as separate SKUs or internal bundling of components to simplify inventory.

Best,
Jiri

1- I don't really understand the difference. If you can explain what a component is and how it is different with a product I can answer you.
I'm struggling here with google translate and what google is giving me for both, is the same thing in our language. Where I work, whatever we produce (manufacture) and sell, is a product. Now, what is a component? The parts within a product? The parts used in an assembly?
Well as I said, we manufacture them and sell them independently (as a single thing) too.
It means if one of our products (assembly) is made of 245 components (parts), we receive orders for those parts individually too. So the components are also products. As I explained above, you can go and purchase the back light of your car after something goes wrong with it. It means your car manufacturer, not only manufactures and sells the car as an assembled product, but also manufacture its components individually (back light) to be sold as parts in case of any trouble/accident.

If you mean something else, I appreciate if you break it down.

2-I really don't understand external, SKU, Knick-Knack. So I can't answer/comment on any of them. I looked up each one and I know what each word means, but I don't understand your comment or question. And again, inventory is out of this topic. As I said, this section of our company, has no inventory. We receive orders and manufacture what is ordered. Not +1, not -1

 

[KitaYama]

I simply refuse to believe that anyone in real world would be so nebulous about his/her environment and tasks at hand as the author of the OP.

It's the third time you accuse me of lying.
And me for the third time: Follow your instincts. You may think whatever you want about me. I don't have any objection and feel no obligation to explain myself.

 

[KitaYama]

@CJ_London

can a kit have components which are other kits?

Yes, and actually it's what prevents me sleeping at night and is bothering me. I can't come to a conclusion how to solve the table design.

Are products specific to a kit

Not particularly. Many of our assemblies share the same products. For example if we need to add a fuse to an assembly, as far as the design allows we try to use the same fuse bracket we have. Otherwise we design a new one and add it to our product list. We try to design the products to be able to be re-used again. It helps us to bring down design & manufacturing costs.

Do kits have options for different products

No. Never. There's no option for any product by any means.

Thanks for your help.