Database model and calculated balance, mysql

Question

Another question from an app programmer who's trying the first time to get together a DB model himself.

In my app, there are users, accounts and transactions. I initially had all tables in 3NF (or so I believe). Then I decided to add a balance field to users, mainly because my code will go open source and I do not want people to mess up the business logic of the system by changing PHP code. So triggers and stored procedures update the balance.

Now there's the new requirement that users will have on their account page a list of all transactions they did with a balance column, so they can watch how their balance changes with every transaction. Of course, transactions and users are in different tables.

How to go about that? My current solution sketch sees a balance_history table with foreign keys to transaction_id and user_id. Any other suggestion? Thanks.

Answer 1

Storing calculated values in a database is always fraught with potential error. That being said, it's just a caching technique, and caches are pretty well understood by now.

However, is it that expensive to just calculate the running balances on the account page each time it's visited? We're just talking addition and subtraction here. Perhaps just by taking the final balance, which is cached, and calculating each transaction's running balance backwards from it might be possible, and save a lot of work in maintaining your proposed balance_history table.

I'd also add that the balance_history table will be a pain to update if older transactions are allowed to be inserted or updated. You'd then have to update each successive transaction for that user to correct the running balance.

Alexey Sviridov's suggestion of using views for the running balances is a good one, although they can be tricky to write (especially to run efficiently). Oracle's analytic functions are really nice for that sort of thing, but I'm not sure if MySQL has an equivalent.

Answer 2

If your transaction table not very big I'm recommend to you remove balance column from users table. For why balance_history if you already have a transaction table? And i'm HIGHLY recommend to you remove any bussines logic from triggers! Only stored procedures. I'm recommend using triggers only for true transparent operations (audit, complex validations and so). In summary i'm think you sholuld remove balance column from users table and instead write view ('UserBalance' for example) joining users and transaction table. Remove any bussines logic from triggers, call aproriate stored procedures instead. Use only transaction table for showing balance history. This all true for not very big tables (3-4 millions of records is ok). For very big databases you need use distributed caches, vertical databases and so on.