What do you want to put in your inventory table? Is this where you will have transactions recorded? Like, 10 came in, 8 came out? ... or ...
Item has many transactions
Order has many transactions -- inventory is just a count
so the transactions table is a join table between Item and Order
Where do you want to work from when creating transactions?
Each transaction record is going to need a foreign key that echos the item ID from the item table, and
Each transaction record is going to need a foreign key that echos the order ID from the order table
I'm probably missing something here ...
The Inventory basically tracks what we have on hand. I will be able to calculate usage within a time frame.
I have the three tables
ORDER Order ID
ITEM Item ID with On Hand and To Order fields
JOIN Order ID, Item ID, Inventory ID and Inventory Date
Since I absolutely do not want to recreate the entire items list each time, I used a portal to link the Item List with a cartesian sign "X". Consequently, when I enter something for either the On Hand field or To Order - it changes the information in the other record as well. which of course, I don't want.
I was able to create a portal on the ITEM TABLE to view previous inventory counts, but of course, this won't work correctly until the Join Table works correctly.
Where do I go from here?