Database design?

Question

We have a Sales related project.

Now we are keeping the stock of the products in a separate table named Stock. At the time of sales, sales-return, purchase and purchase-return the stock table will be updated. It works well, but while we are deleting or modifying one of the sales or purchase, it's more difficult to maintain the stock.

I told my boss, that we don't want to keep the stock in a separate table, but write a function for calculating the stock from related tables (sales, purchase, ...). Whenever the user want to know the stock, they call the function to get the stock very easily. Thereby we do not need to think about stock maintenance. I think its an good idea.

But he told me, that if lots of records will come, the function will take more time to execute and it will reduce the efficiency of the software. I don't know if that's correct or not. One thing i know is that it's against normalization of DB. We don't need to keep the calculated values in or outside the table.

How could i design this DB? Is a separate Stock table better or not?

Answer 1

There is an excellent study on that subject here, by Allen Browne.