Here's my database structure as it stands today...
inventory_transactions store movements of inventory with quantity_offset value that is either negative or positivie. They also have an inventory_transaction_id
shipments store shipments, which are groups of inventory_transactions with a shipment_id
The relationship between inventory_transactions and shipments is in a table called shipment_inventory_transactions
What I would like to be able to do is increment the quantity_offset of an inventory_transaction that is associated with a given shipment (increase the quantity of a given inventory item within the shipment) if that item already exists in the shipment.
If the item doesn't exist, create the required rows in inventory_transactions and shipment_inventory_transactions
I think some combination of JOIN and ON DUPLICATE KEY UPDATE can do this, but I can't wrap my head around it.
To simplify the situation, I'm considering removing the shipment_inventory_transactions table because the relationship between shipments and inventory_transactions is now going to be 1-to-1. The only gotcha here is that each inventory_transaction can either be associated with a shipment or a receipt, but not both. Storing them both in the same column sounds skanky. But I don't love having an extra column in every row as only one or the other will be used.
Wooh... Brain dump complete. If this made sense and you can provide a sensible answer that has eluded me, I'd be most appreciative.
