-2

Given an app that manages appointment bookings, I'm separately storing purchases, bookings made, or cancellations (some of them non-refundable) and am required to show the sum of these, based on different conditions, in the form of one number.

Right now the application will fetch the entire dataset and recalculate this count every time a transaction (purchase of packages, booking made...) is made, but his is too expensive in terms of resources (and money as well). So I'd like to mitigate this by storing the count in its own field, and while keeping the mechanism of storing the transactions individually for history purposes, the ideal scenario would be if I could just keep updating this one field and simply getting the value of it instead of having to constantly recalculate.

The question is: how often would it be practical to still do a recalculation (self-check or audit) to ensure the count is still correct? How can I decide what best audit points should be?

Someone I asked told me to do the recalculation, say, every 3rd time the user logs in. I was thinking I could do the recalculation only if there was an error during any of the transactions, but that might not be sufficient.

Or is this a question that could really only be answered very vaguely or subjectively...?

3
  • 1
    "Or is this a question that could really only be answered very vaguely or subjectively...?" It's not a question anyone here can answer, certainly. What is the cost to your business if things go out of sync? How likely are those errors to occur? Mar 4 at 16:53
  • @PhilipKendall yeah, I thought so, it’s difficult to say, I was just hoping there were some general rules of thumb in similar situations…
    – benomatis
    Mar 4 at 16:57
  • "how often would it be practical to still do a recalculation" - this entirely depends on the performance and the impact it has on the user experience. The "general rule" is that you have to measure this impact with realistic data, and then make a decision based on that data. I would not call this "vaguely" or "subjectively", it is a matter of taking the time to analyse the problem thoroughly.
    – Doc Brown
    Mar 6 at 7:00

2 Answers 2

3

Audits are really a business requirement more than a technical requirement, but businesses do not typically continually audit their books back to the beginning of time. You audit back to the last audit.

I'm probably dating myself, but it's like balancing a checkbook before online banking. You keep your own local tally of what you think your balance is, then once a month the bank sends you what it thinks your balance is and a list of all the transactions since the last statement. You subtract all the transactions in your local ledger that haven't cleared the bank yet, and hopefully your balances match. You don't need to add up every check you've ever written since you opened your account.

1

I think the general rule here is "trust database transactions"

ie, you can keep a running total and trust the database not to allow two processes to simultaneously update it, making it incorrect.

If you can't for whatever reason, then avoid doing it entirely. For example I might allow you to make a purchase without reference to your current balance, just pay me for this order. I won't tell you what your balance is, because I don't have time to generate an accurate answer.

Then have a separate offline process for catching up on refunds or extras or whatever, each month/year/quarter add everything up and look for discrepancies

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