2

I'm trying to design database structure for personal finance application.

Basically I'll have Transactions table, which would store expenses and incomes, besides that I'd like to have another table (I suppose Budget table) to store current balance.

Is it right approach to store current balance and updating it every time I have to insert a row into Transactions ?

Maybe it's better to calculate it according to transaction type (expenses or incomes)?

There's a similar question here , but I suppose there are better ways to deal with this kind of data.

I suppose that there is no best practice and it almost always depends, but which db structure should I choose?

Thanks.

Community
  • 1
  • 1
hgulyan
  • 8,099
  • 8
  • 50
  • 75
  • 1
    Unless you have a large number of transactions for each user it is almost certainly better to calculate the balance each time it is displayed. SQL is pretty efficient in that regard. – Robert Harvey Jan 03 '12 at 23:14

2 Answers2

5

Its almost always a recipe for disaster to store calculated information in a database. Its incredibly easy for it to go out of sync.

You're much better off calculating it when you need it, or if your database supports it using a View.

Conrad Frix
  • 51,984
  • 12
  • 96
  • 155
0

This is not a black or white answer it depends on your non functionals i.e. the number of users that access your website. You could have a best of both worlds solution where you calculate only when the underlying transactions are updated. This is the approach I used when creating a personal budgeting website.

It means that the calculations can be read very quickly and re-calculation can occur in the background, invisible to the user but optimal for most configurations.

Snappy Budget
  • 21
  • 1