Problem Details:-
Table 1:- Product_Country A
Description:- Contains Product ID and other details along with Country Code.
Table 2:- Product_Outlet B
Description:- Contains Product ID and other details along with Outlet Code
A country can have many outlets, suppose country Australia has 50 outlets. So suppose if i update any details of a product in table A, i want to update the same detail in table B based on some if else conditions.
Points to consider:- 1.) Table B is having 50 times more data than table A.
2.) There is a Java application through which we update table A.
3.) There are some rules to update the details in table B, some if else conditions based on which we update and create records in it.
Current Solution:- There is a trigger which puts entry in a Temp table while updating/inserting A, from where a PL/SQL job scheduled twice a day, picks up the data and update/insert in the table B. This solution was considered, because updating the B table right after A table will consume much time and the application will be unresponsive.
Solutions Considered but rejected:- 1.) Updating table B right after A table, will consume much time.
2.) Increasing frequency of scheduled job, would hog the Database.
More Solution Proposals??????
A solution would be to have a "product" table, with references from table A and table B.
So if you update product for country in the A set, it's instantly updated for occurrences in the B set as well.
This means review your data model: a basic rule is you should not have replicated information in your database.