Hi
I have a database to analyse financial data by month/period. I have a main data/transaction table (increasing by avg 20,000 records each month) which looks up various details from other tables and performs quite complex calculations to produce end of month reports for cost deferrals to the balance sheet.
One of my tables stores project codes (each transaction relates to a project code). And this is a main lookup table for the data to determine whether a transaction is deferrable or not, prior to applying rules for the deferral rates, exclusions and various other criteria. It holds around 25,000 records and increases by around 200 each month
I can manage new codes updates for the table with a form for a user to transfer new codes to the project code table and assign deferrable or not. Unfortunately a handful of the existing project codes can change status going into a new month. Which obviously will effect prior month reporting.
I do not want the database to have to hold 25,000 records for each period just because a handful of them change status, as this will severely impede the performance of the database. What would be the best way to manage the changes whilst preserving the historical reporting?
Many thanks
Richard
I have a database to analyse financial data by month/period. I have a main data/transaction table (increasing by avg 20,000 records each month) which looks up various details from other tables and performs quite complex calculations to produce end of month reports for cost deferrals to the balance sheet.
One of my tables stores project codes (each transaction relates to a project code). And this is a main lookup table for the data to determine whether a transaction is deferrable or not, prior to applying rules for the deferral rates, exclusions and various other criteria. It holds around 25,000 records and increases by around 200 each month
I can manage new codes updates for the table with a form for a user to transfer new codes to the project code table and assign deferrable or not. Unfortunately a handful of the existing project codes can change status going into a new month. Which obviously will effect prior month reporting.
I do not want the database to have to hold 25,000 records for each period just because a handful of them change status, as this will severely impede the performance of the database. What would be the best way to manage the changes whilst preserving the historical reporting?
Many thanks
Richard
