FIFO Inventory method

[DukeS]

Hi!

Has anyone implemented FIFO method for updating inventory to get real stock value? I don't think that this is possible to accomplish just with relationships. I am thinking to create one table which would have records of orders and sales and with it I could track how many items were sold for what price. Record would be added to this table when I would set invoice status to locked and run script.

Is stock table usable like this? I don't know what are best practices to move products from one warehouse to another.

I've atached schema of database and xls file to show how I meant to create whole thing. Is it totally missed or am I on the right way?

Thanks

DBSchema.zip