Better to store miscellaneous metadata in database or calculate on each access

Question

I have a number of attributes I need for various page loads and other backend tasks, and I'm debating on whether storing these things in a database or calculating them on the fly.

For instance, if there are files that users can upload, and you want to track the size, space taken, format, etc. would it be better to calculate these things once and store them along with the location of the file in the database, or just grab the file each time and get the file attributes manually?

Another use case is shopping cart items. Is it better to calculate the price of an item and store that in a row with the shopping cart table, or calculate the given price each time a page loads. In this case, changes to the price based on site-wide sales, discounts, markups, etc. would not be reflected once the item has been added to the cart unless the prices are updated through another method when sales/discounts/markups are applied. This isn't the best example, but hopefully you understand the idea; maybe you have a better example.

In both of these examples, the source material is available to get the answers from which is key to the question. Obviously, one has a lot overhead for every page load would could be a lot depending on the situation, however the other seems to have less dependence on database integrity in terms of making sure it is always accurate and up-to-date (which I think I prefer). I'm not looking for a specific answer here, because I'm sure it will depend on many variables, but I am looking for a best practice or a method to determine the best solution.

NOTE: This is a similar question but has gotten very little response and no answers.

Answer 1

There can be trade offs between

• When a user is waiting, elapsed time is critical.
• A user will expect up-to-the-second pricing information.
• A user will be frustrated if he orders something, only to find out that you are out-of-stock before his order is submitted.

We cannot say how fast it will be to recalculate things in your system. If you have some SQL code, we can help you speed it up.

Sometimes in Computer Science, it is faster to do the computations when the thing changes; sometimes it is faster to compute when it is requested. It sounds like your case is that it is also slower to compute when it is requested. If it is not "too slow", then that option may be viable.