Topic: Another Database best practice question (Page 1 of 1)

Hello, another procedural question.

I have two (or more) numerical fields in a a database. When these numbers are shown, they will be displayed with a 3rd (or more) field with basic mathematical calculations of the given numbers (averages, percentages, etc).

In general, is it better to calculate and store these additional numbers in the database, or store only the raw numbers and calculate the others while querying/displaying the data?

Off the top of my head I would say just store your values in the database, and not your products (results of calculations).

The reason for this is that should your values change you will have to also update all your other fields each time.

If I chose to store forename and surname in a database, I don't also store 'full name'

Now really it depends on the amount of server side work you'll be intending to do to create these calculations, it is possible I suppose that you'll save more resources by pulling data from a DB rather than calculating on the fly, but from your example I imagine that you'll be fine.

quote:

---

In general, is it better to calculate and store these additional numbers in the database, or store only the raw numbers and calculate the others while querying/displaying the data?

---

Better for what ? speed, size, maintainability ? In short :

• for speed, try to denormalize as much as possible.
• for size & maintainability, well ... don't. Keep your stuff normalized and clean.

quote:

---

for speed, try to denormalize as much as possible.

---

And note that this does not always work.
Denormalization can also mean having to read way more data from disk,
which negates the benefits of having to read from another table (especially if you retrieve
a lot of rows, and the second table is small, but the exact tradeoff is hard to find).

For simple calculations, even having another 4 bytes to copy around (in memory) per record
will easily exceed the time needed for the computation. (Not to mention the inital costs of storing
the results ).

So long,

->Tyberius Prime

Thanks for the input.

Sometimes I think too much about these kind of questions and end up confusing myself

We're only talking basic arithmetic, so calculate on the fly it is