If I understand your table correctly, it contains data that does not belong there. For example, assuming that one product can be bought by more than one customer, and first I buy this product and then my friend does, what will the cust* fields and itemOut contain?
-- only stuff that describes the product itself. no data regarding supplier specific stuff, orders etc
create table products (
id,
name,
weight,
-- your own selling price. must also go in sale_products table
price,
...
)
-- if you only ever have one supplier per product, you could skip this
create table supplier_product (
-- references product.id
product_id,
-- suppliers product id
supplier_product_id,
-- id of supplier, perhaps found in "supplier" table or in a more generic "company" table
-- references supplier.id (or company.id)
supplier_id,
-- i'm assuming batch is an amount restriction when ordering
-- this will also go in the product_order table
batch,
-- price will also go in the product_order table
price,
...)
-- this is for your orders from suppliers
create table order (
id,
date,
...)
create table product_order (
-- references order.id
order_id,
-- references product.id
product_id,
references supplier.id (or company.id)
supplier_id
-- if supplier_product.price changes, you don't want that change to affect previous orders
price,
quantity,
date
...)
-- this is for customer orders
create table sale (
id,
customer_id,
date,
-- some way (tinyint, enum) to represent onhold, ok, delivered, canceled, paid or whatever you need)
-- esp. you need "onhold" or similar to represent an order where you need more items
-- delivered from supplier bofore shipping to customer, so that you can reprocess this
-- after supplier deliveries
status,
...)
create table sale_products (
sale_id,
product_id,
-- price must go here as well so that a change to product.price doesn't affect previous sales
price,
quantity,
-- just like you need status for a complete sales order, you need it on a per product
-- basis. If I order 2 things, and one is out of stock, you need to know which product
-- was out of stock, just as you need to know which wasn't (for when you reprocess
-- the sales order next time)
status,
...)
You will need to decide how to handle "failed" sales orders. For example, if I order 5 A and 10 B, and you have 100 A but only 5 B in stock, how do you store this data?
I'd expect a sales order to be created
I'd exepect two sales order products to be created
But, do you
Reduce your stock of product A by 5, to 95 straight away, and set status for this product in my sales order products to ok
Or do you
Keep your stock at 100 (since you perhaps don't ship A without shipping 😎 for A and set this sales order product to onhold as well?
I'd probably go with the latter since it lets you sell what you have in stock even if there are a lot of other orders where you are waiting for some items from suppliers. Moreover, in the db you can easily see how much of each product you have on hold, which gives you the means to match your next order to your supplier accordingly.
Apart from these things, you can handle the other things in different ways. E.g.
1. insert sales order, set status onhold initially
2. start transaction
3. check for availability of all items
3.a if enough items of each ordered item
3.a.1 subtract ordered items from stock
3.a.2 insert into sale_product with status ok
3.a.3 update sales order, set status=ok
3.b. if not enough of all ordered items in stock
3.b.1 insert into sale_products with status=onhold
commit transaction
if anything goes wrong between starting and committing transaction, you could retry to insert all items into sale_product with status=onhold. If this fails you'd need to also remove the sale entry initially performed. One way to deal with this would be to start transaction before even this first insert, and use named savepoints to control how far you rollback.
