Database design?

Question

We have a project related to sales.

Now we save the stock of products in a separate table with the name Stock. During sales, sales-return, purchase and purchase-return stock tables will be updated. It works well, but while we delete or modify one of the sales or purchases, it is more difficult to maintain the stock.

I told my boss that we do not want to hold the stock in a separate table, but write a function to compute the stock from related tables ( sales, purchase, ...). Whenever the user wants to know the stock, they call the function to get the stock very easily. Thus, we do not need to think about stock maintenance. I think it is a good idea.

But he told me that if a lot of records come, the function will take longer and this will reduce the efficiency of the software. I don’t know if it is right or wrong. One thing I know is that it is against DB normalization. We do not need to store the calculated values ​​in the table or outside it.

How can I create this database? Is another table better Stock?

Answer 1

There is excellent research on this here , Allen Brown.

Answer 2

, . , , , .

, , . , , . . , .

, , - .

Answer 3

, , ( ). - , . ( ) - , .

(), , . . , (.. ), , . ( , " ", , , - ).

Answer 4

?

. . , . , , , , .. , - . , .

. , , . , , , .

, . , , , , , , -, ( ), , , .

Answer 5

What is the contents of your stock table? Do you have different stocks where you can store the same product? If there is only one stock, I would advise separately from a separate table or estimated stock. Calculating stock can be quite complicated and time consuming, especially if you have to request it more often than perform updates. Then you must make a stock portion of your product table.