I have tables A, B, C and D in my db. I need to create table E in the same db. Table E is to be updated by data from tables A, B, C & D.
Some fields in tables A, B, C, & D have currency as datatype. These currecny data types are to be merged and updated on weekly basis in table E.
How do I manage this?
Do these calculations need to be stored or can they be worked out on the fly?
If the source data will not change then it may be preferential to store the values. You would have to create a job to perform these updates on schedule. If you tell us what RDBMS you are using then we may be able to help further.
If the source data can ever change then you may want your calculations to be on the fly - meaning they are calculated upon each request. This can be achieved using views. Remember that if you have complex calculations and a lot of data then performance will take a hit here.