Which way to create this inventory in mysql

Question

I'm trying to create a web app based on a few mysql tables and forms.

I have the ADD Shipment page in which the user adds a list of products and a Invoice no., so that in my Shipment_Products table I have

id invoice_no product_code qty
1     34          HP222     4
2     34          HL234     1

I also have a Sold page in which the user adds a list of products sold from his stock, so the table Sold_Products get filled like this

id invoice_no product_code qty
1    1           HP222      2
2    34          HL234      1

I need to have a third table called Stock in which I have to get the total number of items in stock, but I'm stuck on how to auto_generate it based on these two existing tables and then keep it updated.

Any suggestions ?

Answer 1

I don't think what you want is as simple as what you might be trying to short-cut. First, if your company is dealing with FIFO vs LIFO inventory, you take inventory out of counts as they were available for COGS (cost of goods sold) purposes based on the cost from a given vendor at a given purchase time.

You might just have a purchases table showing every item and count received, then, as goods are sold, have another table showing the reduction from each purchase received available quantity out. It is somewhat tricky to deal with, given such a simple scenario as...

Purchase 10 of product "X" from a vendor, then another 4 of product "X" shortly after. Total of 14. Now, for sale activity, you have one order selling 3, then 2, then 4, then 3. Total of 12 of the 14 sold, but whatever way is computed FIFO vs LIFO, there would be a split of which quantity was accounted for from which originating purchase... Ex: FIFO would be accounted for from the original 10 then 4 puchases.

10 purchase
10 - 3 = 7
7 - 2 = 5
5 - 4 = 1
1 - 3 = -2 -- WRONG... Only 1 of the 3 against the original 10 purchase
1 - (1 of 3) = 0
4 purchase
4 - (remaining 2 of 3) = 2  -- here is the remaining 2 entries from the last sale, leaving 2 left in inventory.

So, no absolute table structure to handle, but I think you should check how the inventory management requirements may need them.

Answer 2

I'm going to try and explain it better. Sorry for my bad english !

I now have a table received in wich every item in a particular invoice is added when it arrives, so let's say today we receive some items, 3 items and the unique invoice_id or shipment_id (doesn't really matter) is S1. In the table received I have

id shipment_id product_code qty 1 S1 HL223 2 2 S1 XLS21 1 3 S1 BenqWHL 1

I have another similar table called sold wich works the same way, I add a list of items by their product_code and give the sale an ID (for other purposes). So the sold table looks like this:

id sold_id product_code qty 1 B1 HL223 1 2 B1 XLS21 1

Now, I just need to see the items left in stock, either by creating a table in wich I store the items grouped by their unique product_code and just count the entries as qty and then maybe when doing a sale I can substract the qty sold in this table stock ?

I don't care about invoice numbers or IDs, users, etc.

Answer 3

This is the bad way, but at least is the way to begins.

make a table: inventory/Stock or something with the next structure: ID, Item_id, item_quantity.

When purchase/receive, add a row to this table with a positive quantity. When Sales/output, add a row to this table with a negative quantity. To calculate the stock perform a query with the sum() of a particular item_id.