3

I'm making a personal finance management application in PHP and MySQL, for learning myself some more PHP and MySQL.

This system is currently using 4 tables:

users, this table holds all the user accounts with the following columns:

  • ID
  • First name
  • Middle name
  • Last name
  • email
  • password

users_balance, this table holds all the user's balance's. The user's balance is getting updated everytime the user gets a new income or expense, after a certain date or time ofcourse. This table has the following columns:

  • ID
  • user_id
  • balance

user_incomes, this table holds all the incomes for the specific user. This table has the following columns:

  • ID
  • user_id
  • title
  • description
  • date
  • time

user_expenses, this table holds all the expenses for the specific user. This table has the following columns:

  • ID
  • user_id
  • title
  • description
  • date
  • time

The user can view it's current balance, but what if the user wants to check it's balance from a specific date?

Do I need to subtract and sum up all the expenses and incomes back to that specific date or do i have to create another column which keeps track of every new user balance when a new transaction is created?

1 Answer 1

2

If you look at incomes and expenses tables, you see they are the same, they differ only in a sign of the change of user balance. You can easily create only one table of "transactions" to keep both and either add a column with a type [Income, Expense] or decide it only by a sing on a value column (I suppose you want that one as you talk about summing all expenses and incomes, but it is not defined in your question).

Then you can do simple

select sum(value) as balance from transactions where user_id = X and datetime <= Y;

As you can see, I use datetime as one column - I suggest you do do that as otherwise all conditions for checking ranges get quite cumbersome:

where date < xxx or (date = xxx and time <= yyy)
-- and now add once more for the upper limit

This is good enough if you need that only sometimes and have a good index with user_id and datetime and preferably a value too. But if know that you will check that often or that there will be really lots of transactions for each user, it makes sense adding one more column where you will store the value of user balance after applying that transaction. That way getting the user balance for a given datetime is like

select balance_value 
from transactions 
where user_id = X and datetime <= Y
order by datetime desc
limit 1;

with again using index on (user_id, datetime) for a really fast retrieval.

This can have a secondary usage too - it lets you simply verify the users_balance records and check that there is no transaction missing. That can come heplful in a case of a bug or problem with data storage.

7
  • Alright, so basicly if you want to check the balance more often, you would need to register the users balance everytime a transaction has been placed?
    – Bas
    Jul 18, 2015 at 19:31
  • You can compute it each time, with index over (user_id, datetime, value) it is only a range scan of part of the index, which would not even touch the table (as the value is contained in that index). Computing it is quite fast unless the user has tens of thousands of thransactions. It all depends on your needs and expected size of your db. Adding the column gives fast retrieval no matter the number of transactions and possibility to check sequences, but means writing more code to store it. Do you expect millions of transactions to process? Is it worth the added code? It depends on your case.
    – jkavalik
    Jul 18, 2015 at 19:43
  • As i've said, it's more of a practice project. But what would be better in terms of performance, the extra column or the subtraction and adding nummers?
    – Bas
    Jul 18, 2015 at 19:47
  • For performance having it stored means no computation needed. And the drawback of keeping one more columns is usually really small and is "distributed" uniformly through time - small price with each new transaction with big savings on each retrieval.
    – jkavalik
    Jul 18, 2015 at 19:50
  • Alright, so i could basicly just save it after each transaction? About my design, by my personal preference, i would prefer having multiple rows in the users_balance table with a date and time. Is that alright aswell?
    – Bas
    Jul 18, 2015 at 20:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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