sorry when the title is not so obvious. I'll attempt to explain now:
I've two tables: table A and table B. The relation together is a (for any table) to a lot of (for B table). So, it is something like master-detail situation. I've got a column 'Amount' in table B that's clearly decimal along with a column 'TotalAmount' in table A. I am trying ti learn how to keep your value in table A current. My suggestion is to create a view according to table A with aggregate query counting the Amounts from table B. Obviously using the proper indexes ... But, my team-mate suggest to update the worthiness in table A each time we change something in table B from your application. I question, what would be the best answer here? Might be another variant?
Some clarification ... We expected this tables to become the quickest growing tables within our database. And Also The table B will grow so much faster than table A. The commonest operation in table B is going to be place ... and almost little else. The commonest operation in table A is going to be choose ... although not only.
I see several options:
- Make use of an place trigger on table B and perform the updates table A as the friend indicates. This can keep table B as current as you possibly can.
- Possess a scheduled job that updates table A every x minutes (x = whatever is sensible for the application).
- When upgrading table B, do an update on table A inside your application logic. This might not exercise should you update table B in lots of places.
For those who have just one devote your application in which you place new rows to table B, then your simplest option would be to transmit an
UPDATE A set TotalAmount=TotalAmount + ? where ID = ? and pass the values you simply accustomed to place into B. Make certain you wrap both queries (the place and also the update) inside a transaction so either both happen or none.
If that is not simple, your next choice is a database trigger. Browse the paperwork for the database how you can create them. Essentially a trigger is really a small bit of code that will get performed when tips over within the DB (inside your situation, when someone card inserts data in table B).
The vista is yet another option but you can get performance problems throughout chooses which you'll want to find difficult to resolve. Consider using a "materialized view" or perhaps a "calculated column" rather (however these may cause performance problems whenever you place/remove posts).
If the value will change a great deal, you are best utilizing a view: It's certainly the safer implementation. But better still could be using triggers (in case your database supports them.)
I'd guess your mate indicates upgrading the worthiness on each place while he thinks that you'll want the worthiness quite frequently also it could trigger a sluggish-lower recalculating the worthiness every time. If that's the situation:
- Your database should take proper care of the caching, which means this most likely will not be an problem.
- If it's, nevertheless, you can include which include in a later stage - by doing this you are able to make certain the application works otherwise along with a much simpler time debugging that cache column.
I'd certainly recommend utilizing a trigger over using application logic, as that guarantees the database keeps the worthiness up-to-date, instead of depending on all phone callers. However, from the design perspective, I'd be skeptical of storing produced data within the same table as non-produced data -- In my opinion you need to conserve a obvious separation, so individuals don't get confused between what data they must be maintaining and what's going to be maintained on their behalf.
However, generally, prefer sights to triggers -- this way it's not necessary to be worried about maintaining the worthiness whatsoever. Profile to find out whether performance is definitely an problem. In Postgres, In my opinion you can even create a catalog around the calculated values, therefore the database wouldn't have to check out the detail table.
The 3rd way, recalculating periodically, is going to be much reduced than triggers and most likely reduced than the usual view. It's not appropriate to use anyway may be the icing around the cake :).