Proper database setup - let's discuss and suggest

General discussion on all other topics under the sun.

Moderator: Renato

Post Reply
User avatar
LeXa2
New MMEX User
Posts: 4
Joined: Wed Jan 03, 2018 6:04 am
Are you a spam bot?: No
Location: Moscow, Russia

Proper database setup - let's discuss and suggest

Post by LeXa2 »

To set the theme let’s imagine a person Tom who shares an apartment with his friend Jerry. Tom have got a credit card, a debit card, a bank loan he acquired to pay for college and a daily job with a fixed income. Being a financially responsible person Tom have got a life savings account and he deposit 10% of his monthly income there. Also Tom and Jerry are good friends and they do borrow each other cash on a daily basis. Sometimes these mutual micro-loans are a direct cash exchange (“Hey, Jerry, I’m low on cash, could you please borrow me 100 bucks till friday?”) or it might be one friend paying for another (“Hey Tom, there was a delivery for you while you’ve been away, I had paid for it from my credit card.”). Looks familiar, don’t it?

With the scene set let’s get rolling. Tom would like to track his finances and after a quick software market research he settles down on using MMEx. Typical questions he want to know answers for are:
  1. What are his current “totals” including his college loan, his life-savings and his micro-credit situation with Jerry. In other words he want to know “the big picture”, is he still in debt of not.
  2. What is the state of mutual micro-loans with Jerry. How much who does owe who.
  3. How much funds are left to be repaid for college loan (excluding bank interest). Who knows, he might win a lottery and would like to repay this loan ahead of time.
  4. How much funds have he got available right at this moment. This includes amount of cash on hands, positive balances on debit card and “daily” bank accounts – if any and total balance left on credit card till its current limit. He do not want this number to be affected by the funds he owe to Jerry (or Jerry owes to him), by the amount he have got stored on the life-savings account and by the college loan amount left to repay. In other words, he want to know the maximum amount of funds possible to spend right now if the urgent need arises.
  5. How does the general situation looks like for his finances excluding long-term responsibilities. This one is the same as pt.1, but excluding college loan and life-savings account.
  6. Budgeting. He want to be able to plan income and expenses in a non-puzzling manner. In particular he wants to be able to plan for life-savings and college loan payments as if they were withdrawals and want these to be accounted as expenses for budget. For credit card repayments he do not want transactions (transfers from cash of debit card to credit cart) to be tracked neither as expense nor income but still want to be able to budget and track these transfers. Neat but optional feature would be to auto-budget these transfers based on the percentage of the total balance below zero for a given credit card account at a given month day. Same stands for his mutual micro-loan endeavor with Jerry – he want to be able to budget it as planned expense if he had been owing Jerry at the start of budgeting period or as a planned income if Jerry had been owing him.
The question is what is the best way to setup MMEx database to track Tom’s finance to be able to easily address everything listed above. Suggestions are welcome. Let’s discuss it as my bet would be that MMEx in its current form might be lacking some features needed to easily and comfortably achieve above goals without resorting to some kind of “quirks” or “workarounds”. If we would determine what particular features MMEx is missing to fulfill the task it’d be possible for me or any other volunteer to code it in. On the other hand I just might be stupid and missing the obvious ways to properly set up the database. Big thanks to all participants in advance!
User avatar
stef145g
MMEX Developer
Posts: 57
Joined: Sat Aug 14, 2010 3:40 am
Are you a spam bot?: No
Location: Canberra, Australia

Re: Proper database setup - let's discuss and suggest

Post by stef145g »

Based on your scenario, I have created a setup with a few initial transactions. I have also attached the database
------------------
From the scenario, the database would be for Tom with the following accounts:

Database for Tom
---------------------------
Debit Card - Checking
Tom's Wallet - Checking

Credit Card - Credit
College Bank Loan - Loan
Life Savings account - Term

Jerry - Cash

-------------------------------------------------

Question/Action:
1. In what account is his money being deposited?
2. When and how much is he paid.
- Assuming he gets paid $500 per week, deposited to his Debit account
Transaction:
Account: Debit, Type:Deposit $500, Payee: Work, Category: Income:Salary

He wants to save 10% of salary in Life Savings
Account: Debit, Type: Transfer $50 Category: Transfer: Savings

Question/Action:
As for his College Loan to the bank, How much money was loaned, and how much was paid to the college?

Let us assume he borrowed $10,000 of which he has paid the college $8,500 in fees as he requires more money to pay for books etcetera.
Transactions in Account: College Bank
Withdrawal $8,500 Payee: School College, Categry: College/Fees
Transfer $1,500 to Debit Account category: Transfer:Loan

Question/Action
He goes shopping and buys groceries worth $100, of which $40 is for his friend Jerry, and gets $50 to spend later. (Total of $150)

Transactions:
Account: Debit, Type: Transfer $150 to Tom's Wallet - Category: Transfer:Shopping
Account: Wallet Type: Withdrawal $60 Payee: Aldi, Category: Food:Groceries
Account: Wallet Type: Transfer $40 to Jerry Category: Jerry:Groceries

-----------------
This gets the ball rolling as I still have not set up any recurring transactions as yet.
These could be for Income of $500 to Debit account, transfer of $50 from Debit to Life Savings account as these are regular

Could set up added interest from bank to loan as withdrawals to loan account with category: Loan:Interest
and regular repayments as transfers from Debit to Loan Account

How does this setup go about answering the scenario questions?
Attachments
Senario_Tom.mmb
File comment
(256 KiB) Downloaded 398 times
Regards: Stefano
User avatar
LeXa2
New MMEX User
Posts: 4
Joined: Wed Jan 03, 2018 6:04 am
Are you a spam bot?: No
Location: Moscow, Russia

Re: Proper database setup - let's discuss and suggest

Post by LeXa2 »

Stefano, thanks for your reply. Proposed setup seems to be pretty-much "standard" for this kind of situation. Unfortunately it has some problems (i.e. things that I had referred to as "quircks" and "workarounds" in starting post of this thread) which make it not that perfect for the task. On the other hand I don't know any other way that would be noticeably better :-(.

So, what are the problems with proposed setup:

1. It makes it hard to quickly answer the main everyday's question: "how much money can I spend at most today if I would need to?"
Answer to this question would be sum of balances left on all "debit" accounts - wallet, debit card (we assume that debit accounts can't have negative balance in normal circumstances) - plus the difference between the credit limit of Tom's credit card and its actual negative balance. There's no such info available on the "Home" screen of MMEx and there are no built-in reports to cover it. Yes it is possible to implement a "user" report to calculate this one but it is a shame that the answer to such an important - I'd say one of the most important questions in personal finances - can't be easily obtained "out of the box".

2. Tom have to produce "fictional" transactions in order to properly track some micro-loans he gives to Jerry. Imagine that Tom had used his credit card to buy groceries both for himself and Jerry. To track that he has to enter two transactions - a withdrawal from CC to the groceries store (Aldi in your example database) for "his" part and a transfer from his CC to account "Jerry" for Jerry's share. It is not convenient but is ok as long as transactions are entered manually. But it is not always the case. Tom's bank might provide him with QIF or CSV files with transactions on a daily or weekly basis. It is really convenient for him as transactions are being entered automagically into MMEx database. The only thing left for him is to review transactions from time to time to make sure everything is fine with automagic. How should he track his groceries purchase for Jerry in this case? Should he manually enter two transactions as you did in the example database? But later on when the real transaction will be fetched into the database from the QIF it would be hard for him to understand the correspondence between two transactions he had entered manually and a real transaction that came from bank's QIF. Basing on my personal experience it might be hard next to impossible to track things like this if you've got 10+ transactions per day. I guess that there are two ways to make tracking things like this more convenient: (a) allow mixed split transactions so one transaction may perform withdrawal to one payee and transfer to another account at the same time or (b) allow to enter deposit transactions to loan-tracking accounts that do not count towards total income figures but still do change the balance of the account they are deposited to. Case (a) is essentially the same as you had done but without splitting real transaction into artificial transactions. Case (b) seems more convenient to me as for the QIF automation case described above Tom would simply add a "granting micro-loan" deposit transaction to the account "Jerry" right at the moment of the actual purchase and he's done. Real CC transaction would appear in his database later on after QIF import without any extra effort.

3. Budgeting. Let's assume that "College Bank" expects repayment of the educational loan to be made in equal quantities (there's a fancy word for this type of loan repayment - annuitant payments), for example $500 per month. Tom is expected to make a payment to loan's account before 10th day of each month and then on 11th day bank processes the payment, withdraw some part of it as a credit interest repayment and rest of the payment counts towards repayment of the loan itself. How should Tom track this keeping in mind that he would also like to do some budgeting? If he would try to use the "reflect what is really happening" approach - a straightforward one - he would enter a transfer transaction from his debit card account to "College Bank" loan account and would set it to category like "Transfer::Loan repayment". But doing so won't be reflected in the budgeting facility of MMEx with default settings. Tom has an option to include transfers into budgeting totals but there's another catch here. If he would specify "Transfer::Loan repayment" as a withdrawal in budget with expected amount equal to 500 then it would make MMEx budgeting to treat this transfer as a withdrawal - which is correct - but it will treat the status for this line in reverse. "Green check" will be displayed even if Tom hadn't performed the payment at all but if Tom would decide that he want's to do an an over $500 payment to the loan account to make full repayment come faster - it would highlight this budget line with a "red cross" due to withdraw amount being greater than expected $500. Second approach to tracking this one would be to treat transfer as two separate transactions: a withdrawal from the debit card account ("Transfer::Departure from the debit account") and a deposit to the loan account ("Transfer::Arrival to the loan account"). This way Tom do not need to enable including transfers into budgeting totals and he can budget on "Transfer::Arrival to the loan account" as a deposit with expected minimum of $500. Paying less than $500 would highlight this line with a "red cross". Payment bigger than $500 will receive "green check" as expected. But the disadvantage here would be that income/expense numbers would be "inflated", i.e. both would be higher that the real value by the amount of loan payments done during budgeting period. Thinking about this one it seems that possible fix would be to implement more flexible budgeting rules like allowing to define conditions for both withdrawals and deposits for determining the status for this particular budget line. For example Tom might want to define that "Transfer::Loan repayment" is a withdrawal with expected minimum monthly amount of $500 and maximum monthly amount of $1000. Either minimum or maximum or both conditions should be defined for budget line to have a status. Another approach is the same as (b) from pt.2. Tom might track this transfer as a pair of withdrawal/deposit transactions and would like to set "ignore when calculating totals for income/expense for budgets and reports" flag so he would still be able to see non-inflated numbers on homepage, reports and budgeting.

Probably there are more quirks and limitations but IMHO three point listed above are enough to start a discussion. Sorry for a wall of text but the subject is a complicated one with a tricky corner cases so extra details might be for the best.

Fixed some spelling mistakes.
Last edited by LeXa2 on Wed Jun 06, 2018 3:57 am, edited 2 times in total.
User avatar
stef145g
MMEX Developer
Posts: 57
Joined: Sat Aug 14, 2010 3:40 am
Are you a spam bot?: No
Location: Canberra, Australia

Re: Proper database setup - let's discuss and suggest

Post by stef145g »

Sorry for the delay in responding but there is a lot to take in.

As for point 2, I proposed 3 transactions not 2 for Tom's microloan as you put it.

As for auto loading, the bank would not be aware of your microloans so I agree that this is not practical, Checking with the bank, the first transfer transaction would be the only one that would match. The other 2 would have to be manually entered.

The original design of MMEX was to keep it simple with only one account type being "Accounts" and an account could be used for anything being bank, loan, credit cards etcetera. I don't know the complete history but the transfer was implemented in a very simplistic way. This was perfectly acceptable for the original implementation, but in order to keep backward compatibility, the database has been kept relatively in tact with all the extra enhancements that have been provided.

With the expansion of MMEX to do more, we provided account separation and in my opinion the underlying database makes certain functional changes hard to do.

As for your point 3, I have not fully analysed this so I cannot give a positive answer at this stage. I agree that MMEX cannot do a lot of things but for its simple design, it still provides powerful functionality to handle most situations.

To implement features as you are proposing, we need to do a full analysis and redesign.
Regards: Stefano
Post Reply