Mysql – Personal finance database design

database-designMySQL

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?

Best Answer

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.

Related Question