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!