How to design a base for batch accounting of goods?

Question

SQL

Hello everyone Happy New Year everyone .. I apologize in advance for the grammar
I really need your help
Advise with experience how best to implement party accounting for a lot of stock? I read a lot of topics in the forum, I can’t think. I’m making
a simple self-made web application (bicycle) for the store. The store has 6 outlets
The store owner asks for batch accounting in order to find out how many sales were and how much was left for the batch and how much profit loss, etc.
Example brought goods from China 10 bags of goods inside bags different goods 20 pairs of shoes of different sizes and colors 100 pairs of socks, etc.
In the header of the document, enters
Here, as I understand it, the parish ID can be used as a batch number?

Дата закупки   01,01,2016
    Номер документа   1
    Откуда         С Китая
    Куда            Основной Склад
    Кто              Владелец магазина
    Проведен     Да/Нет

Document Details Enters

Код товара      Туфли 43 razmera
    КОД                1                      ----Порядковый код 
    Сколько          10
    Цена              100 руб

Payment according to the document, that is, expenses

Дата                     01,01,2016
    Номер документа   1
    Сумма                    200   -100 для товаров а +100 затраты который он тратил на билеты итд

After arrival, I consider the cost of goods.
Tell me how to store the batch?
Create a table that contains?
ID
IDIncoming
IDWarehouse
IDProduct
Quantity
Price
Cost price
Remaining per lot
How is it better to store or is it better to use a selection?
to show the rest something like this
batch number product code warehouse main store-1 store-2 general
1 shoes (43 size) 1 0 pcs 1pc 2pcs 3pcs
Scheme

Answer 1

1. Directory of goods - contains a complete description of the goods;
2.Incoming documents - contains common fields for incoming documents;
2.1. Decoding of incoming documents - contains goods in the incoming document, linked to tables 2 and 1.
3. Outgoing documents - contains common fields for outgoing documents;
3.1. Decoding of expenditure documents - contains goods in the expenditure document, is linked to tables 3 and 1.
To obtain the balance from table 2.1, subtract 3.1.
I recommend introducing another concept such as "closing the day" :). In this case, you need to create another table of product balances, where you can record the balances for each product on the closing date.
If you do not close the day, then sooner or later you will encounter a situation where the request for receiving balances will be executed for an unacceptably long time. A table with balances will greatly reduce the time to receive the balance of goods.
Other guides as needed.

Answer 2

In sap r / 3, the batch number is stored in the document line (socks and shoes came in one bag and 1 document with two lines is started for this: 1) socks 2) shoes). In any management operation, the material and the batch are dragged together. Material without batch is indicated only in the table with the description of the material. I will describe something like this:
Batch is the number suggested by the system when checking in (the user can choose an older number). During transfers and disposals, the batch cannot be changed.
Other tables according to the description of business processes.

Answer 3

To be honest, I would use a ready-made p / o like 1C for this, with an integration module with your web application. Writing this yourself, from scratch, without special knowledge of warehouse accounting, etc. - will take a fairly large amount of time, bugs and possible financial losses