Updating or creating join table records as needed
my question for today involves join tables, but in a way I have not used them before. My situation:
The company I work for is a distributor, so it has shipments going in and out at all times.
I have a table called Product, which has an InStock field, that keeps the current stock amount for each individual product. When a shipment comes in, InStock value is increased for the relevant products. Likewise, when a shipment leaves the company, InStock values are decreased.
However as it turns out, the company has multiple warehouses. Other than keeping track of the total amount in stock, I need to keep track of how much stock is in which warehouse. I've made a simple data structure for this (pictured)
It is basically a simple join table, that joins Product with Warehouse and specifies how much stock of the given product is in the given warehouse. When a shipment comes, the user will specify which warehouse it will be stored at. Likewise, when a shipment is about to leave the company, the user will specify which warehouse it will be taken from. Should be easy enough.
I've worked with join tables before, I had the classic Product >---< OrderItem >---< Order situation. But this is a bit different. With orders, when a new order came, I just created the OrderItems, set their amount and price values, and then left them lying in the database so that reports could be printed based on them.
However, when a certain amount of a product arrives in a certain warehouse, I can't just create a new StockInWarehouse record. What if there already is one? In that case, I should just increase the amount value for that record. Otherwise I would end up with multiple entries, each stating something different about the actual amount of stock in a given warehouse.
I can't quite figure out how to write a script that would do this. Any advice would be appreciated.