CODEWITHSUNDEEP
databasecaching
Alessandro Desantis
Alessandro Desantis

Reputation: 14343

Is it good practice to store a calculated value?

I'm working on a billing system, and calculating the total amount of an invoice on the database requires quite a bit of SQL. For instance:

Since performing queries is becoming more and more complex with every feature I add, I'm thinking about storing some calculations (net and gross amounts for invoice items and for the invoice). I've seen some invoicing frameworks doing it, so I thought it's not a bad practice per se.

However, I'm a bit worried about data integrity.

Cache invalidation in my application shouldn't be too hard: whenever an invoice gets changed somehow, I re-run the calculations and save the new values. But what if, someday, someone runs a script or some SQL code directly on the database?

I know there are some questions about the topic, but I'd like to discuss it further.

Upvotes: 0

Views: 187

Answers (1)

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230346

Yes, caching is fine as long as you don't forget to invalidate it when necessary (which is one of the two hardest problems in CS).

But what if, someday, someone runs a script or some SQL code directly on the database?

Well, with great power comes great responsibility. If you don't want responsibility, take the power away (forbid direct sql access). There's really nothing else you can do here.

Upvotes: 1

Related Questions