A warehouse system I am working on allows inwards consignments [type = 0] and outwards consignments [type = 1]. The user selects multiple products for each consignment and then multiple warehouse locations for each product.
The system needs to keep track of stock coming in and allow reporting and checking for available stock etc.
When a consignment goes out, the system must select which products to use (based on first in first out or other custom logic). Products from an inwards consignment can go out on multiple outwards consignments. Products from an outwards consignment can come from multiple inwards consignments.
I have designed the following table structure:
products [id, description]
locations [id, description]
consignments [id, description, type]
consignment_products [id, consignment_id, product_id]
consignment_product_locations [id, consignment_product_id, qty, notes]
consignment_outwards [id, consignment_product_location_id, consignment_product_id, qty]
All the tables hold the info for both inwards and outwards consignments.
consignment_product_locations.qty = the qty that the user selected for the consignment
consignment_outwards.qty - the breakdown (detail) of the assigned inwards consignment product quantities.
Is this the best way to design the database?
consignment_product_locations.qty is used for inwards consignments. However, for outwards consignments, it will always be the sum of the matching consignment_outwards.qty values. Is it acceptable to store a calculated field in this case to maintain consistency with inwards consignments and to aid reporting? Or would it be better to fill in the quantity for inwards consignments but leave the qty null for outwards consignments.
Would it be better to split consignment_product_locations into 2: consignment_product_locations_inwards [ id, consignment_product_id, qty, notes] consignment_product_locations_outwards [ id, consignment_product_id, notes]
(NB: this would make reporting harder)