CODEWITHSUNDEEP
database
ubiquibacon
ubiquibacon

Reputation: 10667

Should I store a calculate value in my database along with the variables?

In all the applications I have made where a database is used I typically store the calculated value along with the variables needed to calculate that value. For example, if I have tonnage and cost I would multiply them to calculate the total. I could just recalculate the value every time it is needed, I was just wondering if there was an standard approach. Either way is fine with me, I just want to do what is most common.

If I store the calculate variables it makes my domain classes a bit more complex, but makes my controller logic cleaner, if I don't store the calculated variables it is the other way around.

The calculations would not be extremely frequent, but may be moderately frequent, but math is cheap right?

Upvotes: 17

Views: 6964

Answers (5)

勿绮语
勿绮语

Reputation: 9320

The standard approach is not to store this kind of calculated values - it breaks normalization.

There are cases you want to store calculated values, if it takes too long to recalculate, or you are running a data warehouse etc. In your case, you want stick to the normalization rules.

Upvotes: 16

user1101365
user1101365

Reputation:

This violates Normal Form to have this calculated value. Unless there is a reason to denormalize (usually performance constraints) then you should make every attempt to normalize your tables, it will make your database much easier to maintain/improve and denormalize may lock you into a design that is difficult to alter easily and exposes your data to inconsistencies and redundancy.

Upvotes: 4

eremzeit
eremzeit

Reputation: 4516

It all depends on what resources are scarce in your environment. If you do pre-calculate the value, you'll save CPU time at the cost of increased network usage and DB storage space. These days, CPU time is generally much more abundant than network bandwidth and DB storage, so I'm going to guess that as long as the calculation isn't too complicated then pre-calculating the value is not worth it.

On the other hand, perhaps the value you're calculating takes a substantial amount of CPU. In this case, you may want to cache that value in the DB.

So, it depends on what you have and what you lack.

Upvotes: 3

Simon
Simon

Reputation: 9425

Simple math is relatively cheap, however you need to weigh up the additional storage cost vs performance saving when storing these values. Another thing you may want to consider is the affect this will have on data updates, where you cant simply just update the field value, you need to update the calculated value too.

Upvotes: 0

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95592

In my experience, the most common thing to do is to a) store the calculated value, b) without any CHECK constraints in the database that would guarantee that the value is correct.

The right thing to do is either

  • don't store the result of the calculation
  • store the calculated value in a column that's validated with a CHECK constraint.

MySQL doesn't support CHECK constraints. So your options are

  • don't store the result of the calculation
  • switch to a dbms that supports CHECK constraints, such as PostgreSQL.

Upvotes: 2

Related Questions