I have a doubt in Database designing ?

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

In general, it is best to have a normalized database. If you have data duplication and the software one day doesn't quite work as it should, then you could end up with inconsistent data, which is no good to anyone.

For most purposes, calculations can be made efficient enough that you can do them on the raw data, as you suggest. If this does present a problem in scalability for your particular project, perhaps it would be best to have a table with the calculated values in. This would never be treated as original data, and could be recalculated at any point. Then your data is safe, and your calculations are fast.

The other alternative, depending on your DBMS, is to consider a view.

Answer 2

What's the content of your stock table? Do you have different stocks where the same product can be stocked? If there's only a single stock, I would advise against a separate table or a calculated stock. Calculating the stock can be pretty complex and time consuming, especially if your happen to query it more often than perform updates. You should make the stock amount part of your product table then.

Answer 3

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

Answer 4

Generally speaking, you want more normalization to maintain data integrity, and this also tends to optimize for data updating (you don't need to update the same piece of information in more than one place). The only real exception to this is if your database is going to be primarily used for reporting, and only occasionally updating. Then, the cost of updating in several different places (because you are denormalized) is paid for by the fact that you don't have to fetch information from many different places when reporting.

If your database has to be fast for transactions (updating), and only occasionally report, then normalize as much as possible. It's also easier to keep all the data consistent this way. However, if you are only occasionally updating, and mostly reporting (i.e. reading, or just pulling data out of the database), then your boss might be right and it may be a better choice to denormalize. It's more complicated to handle error conditions and maintain data integrity, though (you have to do more thinking about what is a "logical transaction", and when to back out all of the updates made so far when something goes wrong).

Answer 5

Have you thought about using triggers to update the stock table?

In a perfect world, the sum of all changes equals the current number of items in stock. In the real world, the sum of changes is more of a statistical predictor for the number of items in stock. If you want to make the real world a perfect one, you'd have to account for all possible changes in that number. So besides purchase and sale, you need transaction types for initial stock, for theft, natural disaster, donation to the red cross etc. You'd need dummy transactions for correcting the count if the predictor and the actual number of items diverge for some unforeseen reason. All that is going to clutter your transaction tables.

Your boss's performance argument is valid, too. Every time you need the number of items in stock, you have to sum over all transactions. If you have many of them, this can become a performance bottleneck, even if you index the transaction table properly so you can do the sum from the index.

And don't completely agree on the stock table violates normalization. The number of items you have in stock right now, and the number of items that are purchased or sold are conceptually not the same, they are different numbers that happen to be linked by the business rule that your business is purchasing and selling them (rather than manufacturing and donating them to the Red Cross), and as described above, they are perfectly correlated only in a perfect world.