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
- 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?