ProductStock to manage stock_status for each product
Hi, it's my first post
I'm building ecommerce db. Among others, I have product and stock_status tables. I have to manage different stock status for each product. How about to create another table ProductStock (id, product_id,stock_status_id,min,max) so I can change stock_status (backorder, in stock, out of stock) based on product qty?
So, if you have qty 120 for product #53 you know that it's 'in stock' from ProductStock table and in this case you can easily automatic manage changing stock_status for each product
For that, I wouldn't use trigger because I want to manage stock_status from application side and AFAIK I cannot do that using trigger.