Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.