Inventory: Structuring Many to Many Relationships

[MoGrace]

My title may not be too explanatory, but it should help when searching...

I have an app for tracking a particular asset - knitting machines. I want to add inventory tracking for needles which can be matched to a particular machine based on its current setup which can change - sometimes frequently. Therefore one needle can fit different machines, which will change when the machine setup is changed. I have a master list of needles from the vendor based on the machine setup options that are according to the available cylinders we have for each machine. Currently we have 65 machines and 200 cylinders, and the cylinders are not necessarily model specific (if that makes sense), while others are (we change the cylinder to change the cut, often on a per order basis). I created a Model table to represent a summary subset of the various models with their available cylinders. And since we do not have every single needle in inventory that might be used, I also need a subset of the actual needle inventory where I can track QOH. Oh and just to add more confusion, some needles can be used on different setups as well.

Here is some sample data:

Model Cut Size Setup Needle
FDR-Q 14 26" Rib 5614DH3
FDR-Q 18 26" Rib 5918DL

Now for the fun: another model may have either a 20, 22 or a 24 cut cylinder that will use the same needle, so Model+Cut alone cannot determine which model uses that needle because there is a range involved. I added a field NCUT to my needle and model tables so a 20-24 cut needle can be matched to either a 20, 22 or 24 cut machine. So far so good.

Now I can link my set like this to see which needles belong to which machines:

Model
==> Needles (the master list)
==> Machines

I can't use these tables for Inventory however because the same needle will be listed several times, and not all of them are in stock anyway. This is where I am getting stumped. The potential user (our Head Mechanic) currently is keeping setup data on a scrap of paper he puts on his bulletin board to notify the other mechanics of machine changes - which also includes any reneedling that must be done. He pulls the required needles from the stock room and puts them in the parts room. This is what I need to track so an accurate QOH can be maintained, but also want to provide a history of these setup changes.

I already have the machine info ready so setup changes can be made to that table, but that will only show the current setup. I also have a form for moving machines to a different location, which changes the MACHNO. I am not interested in keeping a history of that either. And all needle purchases are tracked adequately in Quickbooks which I can then use to update the needle inventory. So the purpose of this 'sub-app', is strictly for the mechanic to record his machine changes and note when new needles are needed or used and pull a report to show production what machines are available. And perhaps in the future to also relate these changes to a particular order or style change, although that is merely a dream at this point!

Sound simple? Not to me! Hopefully someone here has already done something similar...

[Al Buchholz]

Re: Inventory: Structuring Many to Many Relationships

Robin

Most many to many relationships require an additional table to relate the items in your master tables.

Sounds like you need that here..

Something like the assembly items in QuickBooks...

[RRTRACKS]

Re: Inventory: Structuring Many to Many Relationships

Robin,

With a many to many relationship between Table A and Table B a set can be defined. As either Table A or Table B could be either a parent or a child, multiple sets depending upon the vantage point would be required.

Having more than one set deal with the same tables is something I have seen discussed here. With zero experience in this area, what cautions you would need while doing this or if this is the right advice for you is not known.

All the Best,
Rich

[Keith Hubert]

Re: Inventory: Structuring Many to Many Relationships

Robin,

As Al has suggested, you will need a third table that simply has every every combination of Machine_Id and Needle_Id. This can result in a table of many thousands of records. As an example, 3 machines can have 10 different needles, this will give you a table of up to 30 records. It will of course depend on how many combinations there are.

I had to do the same thing for a school application. Students, Subjects and Teachers. Most students took most subjects and some teachers taught several subjects. So the Subjects table ended up with over 11,000 records.

Hope this give you some extra thoughts to your thinking.

[Mike Wilson]

Re: Inventory: Structuring Many to Many Relationships

I read this:

1) I want to add inventory tracking for needles which can be matched to a particular machine based on its current setup which can change
2) I have a master list of needles from the vendor based on the machine setup options that are according to the available cylinders we have for each machine.
3) the cylinders are not necessarily model specific while others are
4) summary subset of the various models with their available cylinders

This sounds to me that the cylinders are the "lynch pin". The needles, which is the subject of your tracking, are subject to which cylinders they are/can associate with and the cylinders predicates which machines the needles can be used with. From here, setting up the application is a matter of associating cylinders with needles and cyclinders with machines and then you can get to needle for machines through the cylinders. If this is not correct, then please guide me closer to the facts.

[MoGrace]

Re: Inventory: Structuring Many to Many Relationships

Thanks to all who have responded.

My model table does act as the intermediary table I need to match cylinders to needles which can then match machines to cylinders and the needle(s) to select from.

I guess I am trying more to figure out how to best present this data to the user. My first step is to update the QOH for the actual needles in stock. The master needle list the vendor provided is probably never going to be the same as the needles in stock table. If he must add a new needle to stock then he would look up from the master list and add his QOH - easiest part so far. But if the needle is already in stock then how would I go about showing the history of additions and subtractions to avoid duplicate postings? I kinda need a visual here cause this is where I am stuck. The form would have to provide a search (find) or perhaps a query to locate the correct record. And then there must be some sort of history table to be able to show those postings in a browse. While I am trying to avoid too many steps, I would rather ensure accuracy over speed.

I haven't tried using Quickbooks inventory items or I would probably have a better handle on this.

Here's my steps as I see them:

Show the user the needles in stock and their QOH

If a needle is not found then select one from the master list and add that needle to stock & use script to check for duplicate ( I don't like using field rules for this...)

Same process for taking a needle from stock - except no reason to see the master list for that.

Use a checkbox or radio for determining which action to take and field(s) to show in a conditional object, then let a script append the entry to the stock table with a button that confirms the action.

In the meantime I am working up a sample database to post here.

[Ray in Capetown]

Re: Inventory: Structuring Many to Many Relationships

I guess I am trying more to figure out how to best present this data to the user.

So thats where to start
User : I want to know what choices do I have for a needle for [given]Machinetype/Setup [present to me] optional_master_cylinder_list and [finally] what is my available needle stocking situation out of this array?
Create your set in this order - sounds to me like machine/setup 1:1 parent 1:M cylinder list and 1:M (grandchildren) needles and 1:M /1:1 (g/grandchildren) needles stock.

My first step is to update the QOH for the actual needles in stock.

Standard Inventory system - master stock item (needle), child issues and receipts trail - provides SOH