We have a transaction database for our application. We are designing a solution for budget automation. Budget are heterogeneous in nature. As Sales budget have different attributes and expense budget have different attributes but ultimately figures will map to accounts in our accounting system. budget consumption will be monitored through vouchers in accounts. Now we have two options to go forward
accountid in budget tables and use this as reference while creating different types budgeted vs actual reports. but we are fearing that query may get slow while homogenizing data as both budget and voucher data need to be transformed before comparison
B. Second option is to make separate tables for reporting and transfer data from transaction tables to reporting tables and this design will be query friendly and we will write routines to transfer data from transaction tables to reporting tables (like star schema in data warehouse) . These routines will run periodically.
Is there any other better solution. what things should i keep in mind before going for solution