Help with logic/database flow of a finance app

[guyinpv]

I'm writing a PHP/MySQL app for myself as both a tool I want to use, and a learning excersie for coding.

The app is a very basic finance or budget tool. You can imagine already there will be a type of dated "register" that will list all my bills/debts and other things I want to track. It also will list any income I can project. So basically if I pay $250 on my car every 1st of the month, that will be a record. And if I get paid every Friday $100, that is also in there. If I make a one-time payment somewhere like to Amazon card, it's not repeated. And if I do a side job and cut some grass for $20, that is also not repeated. So I want to store both one-off, as well as repeatable transactions. On top of that I need to edit the dollar amount of repeated transactions. For example the electric bill, which will be different each month.

My discussion is going to be around how to store the data. Currently I have a single table for transactions, it could be an outgoing expense like "Car Payment", $250, due on 1/1/2014, flagged as repeat payment. Simple enough. Also in the same table is income, such as "Company X", $500, 1/10/2014. All transaction types can easily be in the same table, but I don't know if this is a good way to do it though. One problem is how to tell the income to be "every Friday" versus "Monthly" versus "Bi-weekly" etc. Or if I'm paying a credit card twice a month, same thing.

Here is the problem I'm having. I can easily read the database and pull out all the incoming and outgoing transactions and build my register table, but I'm not sure how to handle the repeating bills. Using a simple flag doesn't work well. Let's say I haven't yet paid my car payment on the 1st, and now it is the 5th, I'll need to show the "upcoming" car payment on 2/1/2014 but also continue showing the unpaid 1/1/2014. I can't do this with only one entry in the database using a flag. So how should an app handle this?
Should I have a second table for only the repeating entries? Then duplicate those into the main table as needed?
Should I have my app create the "future" versions, if they exist, each time the app loads?

I ran in to an issue where I didn't touch the app for a few months, so my last entries were like 10/1/2013. In this case, would I then have to create an entry for 11/1, and 12/1, as well as 1/1 just to "catch up" these repeating transactions? It didn't make sense to me to do that. The purpose of my app is to project into the future, not store past transactions. That said, sure I could skip November and December, but what if ther were bills marked unpaid still in October? Maybe I could have a button that just says "catch up" or "start over" and it would rebuild all my repeating stuff for the current month? I don't know!

I don't know if this makes sense, but you can see that I'm having trouble figuring out the logic of dealing with the data and how to store it in the database to handle repeating transactions, as well as "one-off" transactions. Can these exist in the same table? What if I take a payment like water bill and mark it NOT repeating, how would I handle previous records, or already created future records. For example if for some strange reason I mark 11/1 water bill as non-repeating, I would have to change the already created 12/1 and 1/1 existing bills (which would still be marked as repeating in the DB).

This always seems to happen to me when I try to do a project. I get so far and then suddenly the app logic and data starts to get too convoluted and I can't keep track of all the different angles! The other day I opened up Netbeans and all my dev stuff and just stared at it. Ended up doing nothing for like 2 hours trying to figure out how this logic and DB data shuffling will work and how to store each type of record and how to handle the logic of them and date math and all that.

Note that I "know" exactly what I want my app to "do" and what I want to see when I use it, but I'm having a heck of a time figuring how to make it happen!

I think that this could all go into one table, but you need specific fields like these (with examples) - I'm using plain text here, you'd probably want to go with enumerated types or related tables for values e.g. recordType and repeatInterval:

recordType: one-off or repeating
startDate: first due date
endDate: last due date (if fixed period repeating)
repeatInterval: daily/weekly/fortnightly/4weekly/monthly
repeatDate: day of month (if monthly) etc
repeatType: fixed or variable amounts
active: true/false (1/0)
amount: (- or +) [blank for variable amounts of repeatType]

repeatType variable type should have a related table that has a stated amount every interval:

item_id
date
amount

Anyway, that's just off the top of my head.
There needs to be considerable thought with regard to the SQL statement required to pull in all relevant records and to calculate in/out for a particular period. Also, for example if 'weekly', then I assume you'd need to use a php DateTime object for a fixed amount in your processing.

Alternatively, you may find something useful here:

http://www.databaseanswers.org/data_models/

[pritaeas]

how to store it in the database to handle repeating transactions, as well as "one-off" transactions. Can these exist in the same table?

I would store repeating transactions in a separate table. Then, using a daily cron job I'd check whether a repeating transaction should be inserted into your one-off transaction table, and automatically adding an unpaid one if it's date has come up.

[guyinpv]

Thanks for the ideas, it may be a separate table is needed in the end.

Perhaps I'm thinking this the wrong way. My financial "register" is actually less like a register and more like a calendar. If I have a birthday in Google calendar, I can zip forward 10 years if I want it will show the birthday there. Obviously Google is not creating 10 years of new records, it's simply showing a repeated event at the designated date.
My app will work more like this, but at the same time involve some currency math.
For example if I have a car payment due once a month, I should be able to look 10 months in the future and see the payment right there, without having tons of records created in the DB, if that makes sense.

Similar to what you've already suggested, thinking about this as a calendar rather than a register, does that change anything?

[pritaeas]

Similar to what you've already suggested, thinking about this as a calendar rather than a register, does that change anything?

Yes. Then you don't need to create one-off entries for a scheduled one. You can just calculate it for a given month.

[guyinpv]

Except in the case where I would see an existing "unpaid" entry, but also the future entries. How could there be a single DB record but showing an unpaid AND future unpaid bill? Or in the case of electric bill, how would I show a current bill at $xx but future bill change to $xy?
I see a few different scenarios:

I either need to limit the app to displaying one single bill at a time, so the future bill doesn't show up until the previous one is paid.

I could limit the app to only one month with no future projections (don't really want to do this).

Another issue is that not everything is monthly. Entries could be weekly or bi-weekly etc. In that case I would have to show many versions of the same bill (and allow changing of prices on each one) and track which were paid and which are upcoming.
I'm not sure how to get away with not storing multiple rows of the same repeating bill but also track differences in price and which are marked paid. Maybe some field to store a serialized array of specific details?

Maybe there can be some logic where, if any detail of the bill is changed, this triggers a new entry? But if this happens, I'll then have two entries of the same bill with different prices, both set as repeating. It starts to feel messy.

Perhaps once a bill is marked paid, the app creates the next future version unpaid and locks/deletes/archives the previous one so there are not multiple rows of the same repeating bill? After all, it wouldn't make sense to make any significant changes to a past bill that is already paid.

I don't know fellas. How does one figure out all this logic flow? Are there programming tools out there to help flowchart or try and make sense of what the data needs to do?

[pritaeas]

How could there be a single DB record but showing an unpaid AND future unpaid bill?

So it IS financial, and not just a calendar.