Inventory control?

Question

MySQL

Good day, how to make a database structure for maintaining a warehouse with trace capabilities.
1. Invoices (their posting and cancellation)
2. Calculation of the report on the movement (as of the specified date and time)
3. Possibility of conducting a sale retroactively, and all sales after it will be recalculated at cost.
4. Cost calculation for each sale.
5. Inventory acts
The most difficult thing for me to do is to be able to carry out something retroactively and so that everything is recalculated. Cancel something so that everything is recalculated too. What is the best database to choose for this and how to implement all this more competently?

Answer 1

I will offer:
0) IMHO, it is better to make snapshots of the warehouse states for every day (or week or month). Warehouse status - a list of goods (in the form of id) and their quantity. If you need to find out what happened after some invoice, we recalculate everything from the beginning of the day up to and including this invoice.
1) Invoice - just a list of goods (in the form of unique id) and the added quantity. You can enter an invoice type field, you can simply store a negative value if this is a write-off, positive if this is a receipt.
2) For the period, we simply go through all the invoices and record all operations. For identical positions, operations are applied sequentially. As a result, we obtain the movement of material values ​​for the period.
3) Just add another one in the right place between the invoices, then we recalculate all subsequent ones.
4) If we buy goods in batches with different cost prices, then we store them in a warehouse, in batches. As soon as the entire batch is selected, the cost of shipment changes. we ship from another batch with a different cost. That is, there can be 30 chairs in stock, in the form of 3 records of 10 chairs each, but with different addition times and different cost prices.
5) The act of inventory is simply compared with the state of the warehouse at the beginning of the day (see point 0).

Answer 2

Install 1C "Trade Management", where all this is. And the sequence of documents, and the date of relevance, and so on ... This is if for business. And if this is all for some educational purposes, then look at how it's all implemented there.
According to paragraph 3, it is not clear how one sale affects the cost of other sales.