Stock. What is the best way to account for goods?

Question

Database design

Hello friends) The question is probably simple, but now I'm a little puzzled by it. I have a warehouse. Accounting for goods: income, expense, write-off (marriage) . There will be a lot of operations and I had two ideas to take into account the goods:

1. Store everything in one table stoсks , where there will be a type field in which the type of operation will be indicated: income, expense, write-off (marriage)
   
2. Store these in separate tables. Write-off table, income table, balance table.
   

I have two questions:
1. Which way is better to choose 1 or 2?
2. If you choose 2, what is the best way to get the balances by updating an additional table or by using a query? Tell me the nuances)

PS. Do not offer ready-made warehouse accounting systems. I know that they exist, I have my own specifics that do not allow using another similar system.

Answer 1

I would do this:
- Incoming table ( DateTime , FK Invoice, FK Nomenclature, Quantity, Price);
- Expense table ( DateTime , FK Invoice, FK Nomenclature, Quantity, FK Price);
- Table Write -off (DateTime, FK Act of write-off, FK Nomenclature, Quantity, Reason);
- Table Balances (DateTime, FK Nomenclature, Quantity). The table will store the entire chronology of changes in balances (it will be possible to see the balances at any point in time in the past);
In order for the data in the tables to be consistent, it is necessary to write to them in transactions. For example, if the warehouse received 10 pencils, then inside the transaction an entry will be made in the Incoming andRemains . Theoretically, you can do without the Residuals table , since the residuals at any point in time can be calculated from other tables, but in terms of reducing the load on the server, I would leave it.