Inventory Model Brainstorming

[adhoustonj]

Hey AWF,
Happy Monday. I'm trying to draft up a decent way to put together an inventory calculation for a new database that we are implementing.
This is a pretty simple supply/demand model - I would say more similar to receiving a sales forecast, purchasing towards it, and receiving it - that is the goal, make sure all quantities needed have been planned for. No need for invoices, shipping, inventory etc.

A quick summary of the flow leading up to where the inventory comes into play:

• running 5 assembly lines with JIT delivery
• each assembly line receives a 2 week - sometimes 1 week, but standard is next 2 week production schedule
• orders start as 1 model part number of 100 units, which that model needs anywhere from 4-10 parts generated as "required parts" to produce the model
• schedule and schedule revisions comes through Email as excel file
• Import schedule to temp table to parse and clean data - mixed columns such as date/text combined if an order is rescheduled, then the date value is updated to say push/pull etc in front of the original date in the same column.. Grrrrr
• After import to temp parsing table, move into schedule table table with either delete or insert statements. This was the only solution i could come across, as maybe a revision changes the original 1 order with 1 model part number - that i need to generate 4-10 child records as lets call - orderdetails, and has now changed to 6 order lines with 6 different model part numbers, and now all child records need to be updated. So seems like more of a flush/fill update rather than import new orders.
  • maybe I could change a delete to update - where 1 original order line brought in, now 6 new lines - run update first, and then insert when order = order but model_pn <> model_pn. This would at least put 1/6 new models in, and then an insert on order = order AND model_pn <> model_pn to have the other 5. I hate deleting data but like I said, the only thing that I have found

So here is where I am caught up brainstorming - with the delete/insert procedure that works and is validated, I'm wondering how to approach the filling of the orders, and orderdetails, as current open orders will have all new part numbers in order details. When the model_pn changes, ALL OrderDtl parts change. OrderDtl number of parts may even change - one model_pn needed 9 parts, updated model_pn on schedule revisions needs 4 parts.

This leads to my concern of having an allocation procedure that just calculates how much is left to fill an order, or closes out each OrderDtl line, similar to an invoice being issued, payment received, invoice and OrderDtl line closed. Also with the number of parts for the model_pn, and then model_pn/model child parts changing. I was thinking about just running an ongoing calculation based off of orderID order by DESC, and just running a 'calculated view' as they will be sequential by need by date always, and can be filled in that order based off of available qty and each lines required qty for model child parts.
I've looked at Allen Browne's inventory model, and also NorthWind 2.0, but again, most of these concerns arise from the flush/fil/deleting orders that might be in the process of being filled, also making sure we are removing all old scheduled model_pn's, and taking into account the additional model_pn's when deviations are requested.

This is a multi user (5-10) system, most displays are just a status board of current schedule and either qty produced or how many remaining before order is filled, and if we can support next day production schedule, at a most between 5 assembly lines, we are looking at 100,000 records per year of model orders. translated to OrderDtls - 400k-1mil rows.

Any feedback, suggestions, advice, or if you have any good Prayers that have worked for you in the past, my ears are yours.

Thank you AWF.

 

[CJ_London]

Advice - start with the tables suitably normalised to reflect reality. For example if you only have models and parts then the easiest way is two, maybe three tables - models, parts and (assuming a part can be used in more than one model) a one to many table to link parts to models.

Once you have those tables constructed and relationships set, you can then look at the processes and tweak the tables as required to include additional data such as date a model construction changes.

Until you have the tables constructed, difficult to advise how best to handle the processes

 

[tvanstiphout]

I read your post several times and am still having a hard time understanding what you want. This can be because of terminology.

"No need for ... inventory ..."
Could have fooled me, with that subject line and other hints that inventory is needed.

Before we think of queries and importing Excel data, we need a solid relational database design. Post your Relationships diagram as it stands today.

It appears you need to handle part numbers that change over time. Is that so? Does that change the "recipe" for making a product? Presumably there are several products using the same parts (M:M relation) and if a part changes from Version 1 to Version 2, all products not yet created will be made with the new part.

What is a "model part number", and how is it different from a "part number" in common parlance?

It may also be useful if you tell us a bit about the products your company makes, and what parts go into some of these products.

Do assemblies play a role in your production process?

 

[adhoustonj]

Here is my relationship diagram right now. TblTemp is loaded with a no header transferspreadsheet, parsed to tblImportPlan - and then I move to tblPlan & tblPlanDetails.

Model_pn = assembled part/kit
part_number = components that make up the assembled part, with no overlap between 1 part on multiple model_pn's.

When the original excel schedule gets emailed out, that is an export from 'middleware' process, but all revisions are manual excel updates by the product line planners, and then re-emailed out, and then this department has to also manually reschedule their parts that they produce. So it can be a challenge for them to pinpoint where they are on what order and if the model_pn has changed then that order needs to be 're-fulfilled' to the original full order quantity - ex: if model1 for 100pcs was scheduled and 50pcs of all 3 part_numbers it needs have come off of the conveyor, now schedule revision is model2 & model3 for 50pcs each, they are back to needing to fill 100 model_pn's, 50 for model2 and model3.

So in my relationships I originally came here to decide between tblScenario1 and tblScenario2.
These are filled by the person working the end of the conveyor by scanning the part_number barcode 1 at a time. scenario1 would be a calculation on of how many pcs have been scanned and entered into tblScenario1 and compared to how many pcs needed in tblPlanDetails. Scenario2 would be allocating that to a tblPlanDetails.pdtl_id each time at scan.

My concern with both Scenarios is the flush/fill of tblPlan and child tblPlanDetails records each time a schedule revision come across and model_pn's are changed.