Advice on designing this database?

[eluser]kyleect[/eluser]
I apologize for not having more solid code and asking this question but I'm currently in the process of designing a database for an application I'm creating and I'm having trouble... wrapping my mind around the best approach for this.

Ok. Here is essentially the application I'm designing. I want to create a financial forecast for a checking account. I need to be able to declare a starting balance, enter transactions and defined reoccurring transactions. The forecast will then not only provide my current balance but a running total going out 6, 7, 12 months based on both the transactions I've entered and the predefined reoccurring transactions. This reoccurring transactions would be things like rent, electric bill, cell phone bill but also my paychecks. The format of this forecast would be a table in the following format:

date | transaction description | running balance

I hope I've described what I would like to do clear enough and I think it will help provide context for my questions.

Currently, I have 2 tables

`accounts`
-id
-account_number
-description

`transactions`
-id
-account_id
-amount
-date
-description

1) The running balance calculation. Should that be done in the model or controller?

2) I need to store the starting balance of an account so I can calculate the running balance. I did have a column in the `accounts` table called `starting_balance` then I tried having a transaction with a description of "starting_balance" but neither of these solutions seem ideal. Do I really want to keep calculating a running balance based on a starting balance that could be... 6 months old? A year old? Seems dodgy but I can't seem to think of another way to do this. Suggestions?

I am not looking someone to map out my application or even provide any code, just a push in the right direction. Also, I know someone is going to suggest it, I have looked in to various desktop/web apps to keep track of transactions but none of them really provide a view of the data I need.

[eluser]Ben Edmunds[/eluser]
In regards to the starting balance. I would have the first transaction for the account be the starting balance, makes sense to me.

For the forecast you would want to set how often the transactions occur and what day of the month they occur (this includes the deposits). Then you can take the deposits in the date range, plus the starting/current balance minus the transactions in the date range.

I would probably setup another table to track the recurring transactions, the date they recur, how often they recur, etc...


Schema would be:
`accounts`
-id
-account_number
-description

`transactions`
-id
-account_id
-amount
-type (+/-)
-date
-description
`accounts`
-id
-account_number
-description

`recurring_transactions`
-id
-account_id
-amount
-type (+/-)
-recur
-dayOfMonth
-description


Make sense?

[eluser]kyleect[/eluser]
That makes sense. I decided to have the starting balance as a transaction, the reoccurring transactions will have to wait though because some of the reoccurring transactions don't always take place on the same day. Like my paychecks are every other friday so for now I will be manually entering in those transactions. No big deal. I have come up against another wall though. I'm basing this app on a spreadsheet I was maintaining and it structured the transactions in a very specific way.

Here is how I'm currently getting the data:

Code:

SELECT  `transactions`.`date` ,  `transactions`.`amount` ,  `transactions`.`description`
FROM  `transactions`
ORDER BY  `transactions`.`date`

date amount description
2009-10-26 240.00 Starting Balance
2009-10-27 15.00 Gas
2009-10-30 50.00 Pay back parents
2009-10-30 741.00 Paycheck
2009-11-01 40.00 Electricity
2009-11-01 370.00 Rent
2009-11-07 60.00 Cellphone
2009-11-13 100.00 Food
2009-11-13 741.00 Paycheck
2009-11-27 741.00 Paycheck
2009-12-11 741.00 Paycheck
2009-12-25 741.00 Paycheck

The format I want is:

Paycheck
-Pre-budgeted Expenses
-Non budgeted transactions

date amount description
2009-10-26 240.00 Starting Balance
2009-10-27 15.00 Gas
2009-10-30 741.00 Paycheck
2009-10-30 50.00 Pay back parents
2009-11-01 40.00 Electricity
2009-11-01 370.00 Rent
2009-11-07 60.00 Cellphone
2009-11-13 741.00 Paycheck
2009-11-13 100.00 Food
2009-11-27 741.00 Paycheck
2009-12-11 741.00 Paycheck
2009-12-25 741.00 Paycheck

Like I said, we are putting aside the pre budgeted expenses so lets focus on the paychecks and non budgeted expenses. This is at least two queries. One for the paychecks and one for the transactions. I've been trying out different code to the affect of.

1) query for the paychecks, store the result array in a variable
2) for each paycheck, query for transactions between this paychecks date and the next paychecks date

The next step is a bit blurry. I thought that I could append the transactions array next to the spot in the paychecks array for the paycheck I was looking at. This lead me reading about manipulating the array cursor and unfortunately I didn't get very far with that.

The real issue is staying with in the model/controller. This would be a fairly easy task to do, calling php and model methods inside to view but this is not something I want to do. Any suggestions or ideas I might have missed?

[eluser]kgill[/eluser]
Umm are your two examples of data supposed to be the same? Secondly why would you think you'd need two queries? As long as the data is ordered by date, the transactions should fall in the right order between paycheck dates so it's just formatting the view. Stick an if in there, if desc is paycheck print out your header line, if it's not it's a detail record that goes underneath it.

[eluser]kyleect[/eluser]
The two examples contain the exact same information. The first example orders by date (what you suggest) but if you read inside the post, this is not what I want. In the second example, the paychecks are treated like headers. Each paycheck has a date range, which would be the date of the paycheck though the day before the next paycheck. Under each paycheck, the transactions within that date range are ordered by date. I think this also should answer your question regarding why I think this will involve more than one query. I apologize if I sound rude and maybe I wasn't clear enough but all of this information was in my previous posts.

Does anyone else have any suggestions? Thanks!

Note: I really do apologize if I sound rude, I have had a very long day.

[eluser]@rno[/eluser]
Hi Kyleect,

Can you explain why you couldn't use the single query option presented by kgill.
In my opinion these paycheck lines (the headers) are transactions as well, they're just credit transactions while all other transactions are debits.
As kgill states: when comming across a credit line you can display it as a header, when reading a debit line you treat is as a detail record. This way you do not need to have this so called range in your paycheck.

Hope this helps.

Arno

[eluser]kyleect[/eluser]
I was typing out a response when an idea hit me. I could do it in one query but what I need is sort priority. So if a paycheck and a transaction hit on the same day, the paycheck's sort priority is higher than the transaction and would be listed first. I've added a column to my table called `sort_priority`. I originally wanted to avoid this method however it's non destructive and easy to implement. Thanks for everyone's input.

[eluser]@rno[/eluser]
For that sake: why not sorting on the date (and de DATE only, not the time) descending and then sorting on the amount descending as well (so your credit paychecks will get listed first)

Saves you another column in your DB

Regards,
Arno