-1

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
A. Add 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 A or B

0

C. If that's a web app - setup replication, run fast and write queries on the master and slow, reporting queries on the slave.

When database will grow - running backups will be simplified.

0

I would go for option A, even though you should elaborate a bit on

but we are fearing that query may get slow while homogenizing data as both budget and voucher data need to be transformed before comparison

how this data needs to be transformed? you are just adding an id in your table, so that the query will run faster (given that the index is correctly set). Plus, with option B - even though the transfer data routine will run in different times - you are not considering that if you need to perform the query and you are in the middle of the update period, the result will not be up to date.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.