0

How the records are updated in an accounting system i.e., what is the standard practice? do you keep all the records and do the calculations on the fly or you add and subtract in each time from a final amount say "$current_balance".

The first approach of doing the calculations on the fly i.e, summing up all the deposits and subtracting all the expenses is a sure way to get the result but I can see a definite scalability problem as the number of rows grow.

On the other hand accumulating in a variable say "$current_balance" and just updating it has its shortcomings as well.

So what is the recommended practice to approach this problem?

1

Generally accounting systems have a large General Ledger table. Each row on the table is a transaction, functioning more like a star schema rather than a traditional 3NF relational database. In this way they are normally a hybrid of both star/snowflake and relational combining the best of both worlds where possible. They are generally not strict 3NF databases, with some duplication in the GL table in terms of project codes or business unit codes. For instance they will often look like this:

enter image description here

Records are not usually updated in an accounting systems. What happens instead is additional transaction rows are added which reflect the amendment in the previous entered value. For instance, if you wanted to change the Transaction Amount for BU Code A100 above to 50 instead of its current value 100, you would add a line:

enter image description here

If you wanted to delete that line then you would add a row similar to the above, but with -100 in the Transaction Amount (and then mark this as a reversal in the transaction types).

Generally computers are pretty fast at the adding part of this, there are various partitioning tricks you can use to improve performance. All vendors are very fixated on improving the performance, so you usually don't have an issue. With modern DBs indexing, they usually update quite fast though there are often lags.

For real performance the accounting system data is usually put into an OLAP cube, where further speed efficiencies can be obtained and more analytic type queries can be answered.

  • now that makes it all clear. Thx a ton. – OAH Nov 8 '18 at 14:29
  • Note there are some exceptions to "never updated". For instance budget information is often dumped completely from the GL table (deleted) and re-uploaded. This is especially true if the budgeting system is external to the accounting system. – blobbles Nov 8 '18 at 23:12

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.