Generally, is it more beneficial to keep raw data with pre-calculated values within the database and focus on keeping the database up-to-date basically remove or remove a row when using the pre-calculated values for display towards the user

OR

is it more beneficial to keep the raw data and calculate the right display values on-the-fly?


A good example (that is pertinent to my project) could be like the following:

You've got a timer application. During my situation its using Core Data. It isn't attached to the web, but a self-contained application that operates on a pc or mobile phone (user's choice). The application stores a raw start some time and a raw finish time. The applying must display the amount of the big event and also the interval where the occasions are happening. Will it be easier to store a pre-calculated "duration" time or even a pre-formatted duration string that'll be employed for output or will it be easier to calculate the duration on-the-fly, as they say, for display?

Do i think the the interval, although there's another layer involved because after i create/remove/update a row within the database, I'll have update the interval for that products that are influenced by this. Or, is it more beneficial to simply calculate because the application executes?

For that record, I am not attempting to micro-optimize. I am trying to puzzle out the easiest method to reduce the quantity of code I must maintain. If performance enhances consequently, so whether it is.

Ideas?

Generally, you would like to avoid calculated values within the DB (from existing posts/tables), unless of course profiling absolutely dictates that they're necessary (i.e., the DB is underperforming in order to great of the load has been positioned on the server). This really is much more true for formatting from the data, that ought to more often than not be carried out around the client side, rather than wasting DB server cycles.

Obviously, data that's absolutely mandatory to do the information ought to be saved within the database.

Whenever you talk about reducing the quantity of code you have to maintain, bear in mind the DBA must maintain saved-proc code and table schemas, too. Moving maintenance duties from Designers to DBAs isn't getting rid of work, it is only shifting it.

Finally, database changes frequently cascade to a lot of programs, whereas application changes only affect that application.

The only real time I store calculated values inside a database is that if I want it for historic reasons. You will see this constantly in accounting software.

For instance if I am coping with an invoice, I'll typically save the calculated invoice total because possibly the way in which total can get calculated afterwards can change.

I'll also sometimes carry out the actual calculation around the database server using sights.

As because of so many other activities, "it is dependent". For the referred to situation, I'd lean towards keeping the calculation in code. Should you choose opt for the database, you need to use a view to dynamically calculate instead of include a static value. The chance of altering the beginning time or finish some time and failing to remember to alter the duration could be excessive otherwise :)

This really is dependent on wether you need to be pure (keep the data clean) or fast. Compute capacity around the desktop facilitates wholesomeness, high-speed cores and enormous memory spaces make string composition for table cells possible with large data sets.

However on the telephone, an apple iphone 4 even, computing just one NSString for any UITableViewCell over some 1000 objects requires a noticeable period of time, which can impact your consumer experience.

So, tune the total amount to use situation. Duration does not seem enjoy it can change, and so i would precalculate and keep duration And also the display string (feels aweful in the outlook during a DBA, however it will render fast on the telephone).

For that interval it may sound as if you really need another entity, to relate the interval to some occasions. It might then be simple enough to pre-compute / maintain this calculation too every time the connection changes (i.e. you add an entity towards the relationship, update the interval).