Database design, processor vs inconsistency

Question

I was wondering, if I have a table that stores, lets say, a user's transactions, then when I need to fetch the user's current balance I would have to go and use SUM() like SELECT SUM(value) FROM transactions. But that seems redundant in the php + MySQL world because a user can refresh the page 3 times in a second if they wished and what if that user had been using my service for a few years now and has hundreds of thousands of transactions, I'm going to calculate this every time a page is requested?

On the other hand if I create some kind of cache or another table called something like balances where I will only store current balances of users and will update it with triggers from the transactions table - it seems much more rational and does not look like I would be wasting resources, however my broken code sense starts tingling. I can bet that something will go wrong eventually with this set up, some transactions will get lost or will be recorded without affecting the balances table or something, anything really.

Which one would be the better approach and why?

Answer 1

It depends on what you're willing to do in terms of architectural/performance payoffs. It sounds like at the core you're doing a kind of Event Sourcing, which actually makes a lot of sense when you start talking about domains where it's important to have audit-trails for exactly what happened.

I think a key-point for avoiding "code rot" is to make sure that the code-path for "replaying" events is the same as for making changes the first time. Rough example:

// Don't do this
assert(account.getBalance() == 0);
transactionEvent = account.add(123);
recordInDatabase(transactionEvent);    
assert(account.getBalance() == 123);

// Instead try this
transactionEvent = new TransactionEvent(123, account.getId());
assert(account.getBalance() == 0);
account.apply(transactionEvent); 
assert(account.getBalance() == 123);
recordInDatabase(transactionEvent);

With the second-method, you're always using apply() to transform your object, whether the events are "fresh" or whether they are being "replayed" from the database. (You'll obviously need some special handling to prevent external side-effects like web-service calls from occurring during replays, however.)

I'm going to calculate this every time a page is requested?

One performance optimization to consider is "snapshots", where you still keep the entire sequence of events (i.e. transactions) but you also periodically record things like "After transaction #4155, the balance was $255.43".

Then, when you're trying to figure out the "current balance" after transaction #4203, you skip straight $255.43 and start adding the values from all the transactions 4156-4203. Whenever the re-summing process get too slow, record a new snapshot.

Again, snapshots are merely performance-optimizations: If none are available, your code should still be able to reconstruct everything from Transaction #1 and still get the same answer. An offline background job can be set up to recalculate verify the integrity of your snapshots.

On the other hand if I create some kind of cache or another table called something like balances

This could be considered a "Read Model" for a CQRS architecture. Sort of like an SQL materialized view, except that you can reuse your "real" application code/language to do it. If your balances rows contains version-information (like, say, the last transaction ID that was part of the calculation) then you can also detect when something went wrong and it became stale.

Answer 2

When you only needed to deal with Transactions, then a single Transactions table was sufficient. Now you need to know about summarisations at the Account level.

Yes, you could calculate these dynamically, but this will get progressively slower the more Transactions you have to trawl through. This isn't a User Experience decision. How often will a User want to look at a set of Transactions? How often will that User want to know their current balance?

... another table called something like balances ... update it with triggers ... seems much more rational and does not look like I would be wasting resources, however my broken code sense starts tingling

These balances are derived data; you can recreate them at any time so even if your triggers messed up (and you really ought to know about that long before your Users start complaining) you can still recover the required data from the Transactions (and then fix the Triggers).

As for your "something will go wrong eventually" assertion; yes, but it really ought to happen (and be caught) in Testing before it has the chance to happen in Production.