Solved Correct table structure for managing inventory

@KitaYama It's not been a waste of time, it's been an interesting discussion, but personally I think you need to consider the physical process, and make the IT solution work with the process. Maybe the process is a) record the materials used in each production batch and b) use older materials first, but a) outranks b), which comes back to @Pat Hartman and @CJ_London mentioning simple manual processes such as colour coding the batches to make a) work and b) relatively easy to achieve without needing any fancy IT process.

The thing is, how will you tell when a product fails which part was actually used in the production. To me it's not really about your IT system recording the usage as officially being batch 1, rather than batch 2, it's about you can tell the difference between an item from batch 1 and an item from batch 2. One way is to turn this around, and let the stores people pick the product you specify, and then they record the batch tag they actually picked. If you think there should still be 100kg of batch 1 left, but the stores have wrongly issued the last 100kg in a different production run, they will never be able to locate the product you want them to use.

Is there a real reason you need to use a particular consignment of item 1234 in a particular order other than for your IT system to "use" the oldest items first.

So it's not a stores process you need, so much as a batch management/control process that works alongside the stores process. For each batch you record accurately the products used in the batch without this being part of the inventory management at all. You requested 8 items of 1238, and the stock record just records 8 items issued, but the batch record for Batch A124/0912 records specific information about the 8 items that were actually picked. You could do this by barcoding the goods inwards for instance, so that the stores clerks can record the items in the batch by simply scanning the bar codes.

I am sure this is what happens when you register a washing machine you purchase. The serial number is on a plate on the washing machine in a sealed box. There is absolutely no way you know which washing machine ended up with the customer until the customer returns his warranty card to tell you. You know the batch of watching machines you made, and gradually you mark them off as the customer identifies them. And if he doesn't bother to tell you because there wasn't a maintenance issue, there's still not an issue. And I'm pretty sure the serial number management process is not part of the production process. It's more of a free standing after sales activity.
 
Last edited:
@gemma-the-husky I'll pass your comments in the next meeting. Forgive me for not answering most parts of what you kindly explained because I think I've already cleared what we need. Let me explain only this section.
I am sure this is what happens when you register a washing machine you purchase. The serial number is on a plate on the washing machine in a sealed box. There is absolutely no way you know which washing machine ended up with the customer until the customer returns his warranty card to tell you. You know the batch of watching machines you made, and gradually you mark them off as the customer identifies them. And if he doesn't bother to tell you because there wasn't a maintenance issue, there's still not an issue. And I'm pretty sure the serial number management process is not part of the production process. It's more of a free standing after sales activity.
Have you ever visited a tire line production? The tire that you use on your car? I don't know how things are managed there, but I've visited manufacturing line of Bridgestone here. If you have an accident because of a tire burst, they are able to trace back who, when and where the tire was made. They can exactly give you the name of the staff in charge and trace down to minutes of production.

In our case, if some of our products fails in what they have to do and we receive a complain, we are able to give a report which machine, who and exactly when it's been produced. We also have to address a list of maintenances of the machine with some other data.
I don't know about the washing machines but I know about many production lines that are able to control these data.
I don't go into details because it's going to change this thread into an engineering discussion but for short, every one has a key card. They have to insert it into machines to be able to use them. The machines log their operator and production numbers along with the date, manufacturing serial number and a lot of other data, such as inspection date, package date and even shipping date. It's a perfect system and we don't have any problem in that area.

One more time, we have a complete solution for production. But unfortunately it's not able to trace back the production to our used parts/materials. And I think it's out of this discussion. We were looking for a way to add a database to what we have and the single rule was what comes in goes out first.

As I said, we start working on the database and will be back if we faced a wall.
Thanks for your concern.
 
@gemma-the-husky
I'm not allowed to show you everything on a public forum, but it's a part of production of one of our machines. I can give you the exact time of a the produced part. Each color has a meaning. Errors, running time. Program, operator, Did he took a break, Everything that we need to know is there. I hide personal data and product data.

1.png


2.png
 
If you have an accident because of a tire burst, they are able to trace back who, when and where the tire was made. They can exactly give you the name of the staff in charge and trace down to minutes of production.
This is EXACTLY the problem I solved for Pratt & Whitney engines. 40 years ago. I know Dave keeps telling you this isn't important but it IS. It is very important that you be able to identify where other tires from the same batch were sent. You might not be able to identify exactly what vehicle they ended up on but I don't think that is your responsibility. If you sent them to Ford, THEY will be responsible for tracking the VIN of the vehicle they ended up on. If you sent them to a retail outlet, I'm not sure how detailed their records are. They may or may not be able to identify the customer depending on if the sale was cash or credit.
 
@KitaYama we are 44 posts in. I am completely lost. Have we helped to resolve your problem or given you any ideas?
 
KitaYama

You may find articles on "traceability" of interest. I've often wondered how the food industry can take a product recall and trace the "production/batch" back to the supplier, delivery, farm and even animal. I realize your situation is different, but concepts are similar as Pat described for Pratt and Whitney.
Here's one link
 
@KitaYama we are 44 posts in. I am completely lost. Have we helped to resolve your problem or given you any ideas?
resolved? not yet.
idea? I think a little bit.

@arnelgp showed me some demos, they were very useful. Unfortunately once a record is saved, it's locked for editing. He has some correct points why editing may cause problem.
We are working on his demo to change it and make it what we have in mind.
If we face any problem down the line, I'll be back.
Thanks for your time and support.

@jdraw
Thanks for the link. I'll read it in train tomorrow morning.
Just for your information, we have very impressive solution for traceability of our production line. Our problem is that we didn't order its inventory plugin.
Because 1- It was too expensive, 2-It was completely different from what we expected.

Again, thanks to all who took their time and participated.
 
The difference in how they do food and how planes and engines do it is with food, everything is marked as it goes to the consumer or the grocer. But with planes and engines, the identification comes from the manufacturer to the assembler - Boeing, Pratt. Then as the planes and engines are built, the parts used are recorded so they can be traced back to the original manufacturer including the one that made the tires for the plane:) All metal parts are linked to a "heat" (what they call what comes out of that big vat you see at the steel plant where they pour out the red hot liquid metal into an ingot that is then shipped to someone to build something out of. Other type of parts are tracked but not all of them. The application I designed also managed testing. For inexpensive components, we usually did full destructive testing. We stretched it until it broke. We bent it back and forth until it broke, etc. For rotating parts, which were much more expensive, we did things like ultrasounds in a liquid bath and x-rays.
 

Users who are viewing this thread

Back
Top Bottom