Production: should storage tanks share a table or each have their own?
I need some advice regarding the basic logical layout/relationships of my database. I'm tracking production and inventory in a distillery. I have a table for distillations, a table listing the storage tanks, and a table of transfers between tanks. Product can be transferred between storage tanks or to or from a distillation.
Currently a formula field in the storage tanks table is adding up all the transfers in and out to get the current volume. One of the problems I'm running into is that it would be very complicated to get the volume stored at points in the past such as the start and end of February.
My best guess is that it would be easier to give each of the tanks their own table with each record being a transfer. That way I could use a progressive sum (https://forum.ninox.com/t/x2hrtc9) to show the volume after each transfer.
Am I on the right track? Does anyone have any suggestions?
No, your tanks need to be records in a single table. I would have three tables, Bins to hold the tanks and production areas (distillations?), Transfers to hold each transfer and Inventory to hold the inventory. Transfers have two links to Bins, BinFrom and BinTo plus the Date and Qty. Inventory has a link to Bin plus the Date, Qty and Total. Have a button on Transfers with the following script
let a := this; let b := BinFrom.Qty - Qty; BinFrom.(Qty := b); let c := (create Inventory); c.( Bin := a.BinFrom; Date := a.Date; Qty := a.Qty * -1; Total := b ); let d := BinTo.Qty + Qty; BinTo.(Qty := d); let e := (create Inventory); e.( Bin := a.BinTo; Date := a.Date; Qty := a.Qty; Total := d )
This will keep track of all your transfers and the quantity present in any of your tanks on a given day. It creates two Inventory records for each Transfer, one for the transfer out of a Bin and one for the transfer into a Bin. It keeps track of the total quantity at all times.
If these are the only tables you are using then include two virtual Bin records 'In' and 'Out' for initial transfers into your business, and final transfers out. If you are looking for further development of your system please feel free to ask.