Financial db table theory question

[Montez659]

I am creating a personal/financial/budgeting db and I am having trouble grasping a piece of how the budgeting will work.

I have a Main category and Sub category, so I created three tables, tblBudgMain and tblBudgSub, and the JUNCtblBudgMain_BudgSub. In theory, the way that this is created, I would have to store the PK (which is Autonumber field) from the Junction table to the transaction table when a new transaction is entered. I suppose that the budgeting item will be loaded via a cascading combo box (I need to research that more later).

It seems that the combobox to choose the sub Budgeting item would be loaded off of the Junction table which Would then allow me to store the PK of the Junction table into my transaction table.

Am I going about this all wrong, or is this about the best way to do this for a normalized db? I am just looking for direction as to how the structure should be - I haven't really delved too much into building it yet so I can't really test too much.

Thanks in advance for any help/suggestions/links!

 

[Montez659]

So, the thought occured to me that all I would need to store in the transactions table is the sub Budget category ID, as this will be unique if i force the user to create a new record if they would have wanted to move it. I think this is the dilemna that I was having and that would be my solution.

Although any other suggestions would be greatly appreciated.

 

[xenou]

What's your table structure like (without any Budget tables)?
ξ

 

[Montez659]

This is probably the best way to show you. I have included the table structure for the budgeting items too, as I have had a chance to work on them.

http://dl.dropbox.com/u/17000917/tableStructure.png

 

[xenou]

Okay. What's the difference (or purpose) of budget "sub" tables?

 

[Montez659]

Well, the Main table will hold generic values like Car, House, Shopping, etc., where the sub table will hold the more specific values: Car - Fuel, Maintenance; House - Mortgage, Repairs, Home Improvement; Shopping - Food, Clothes, Gifts... you get the point. Each of the sub budgeting items will have a budget amount and that will only translate back up to the main budgeting items via reports.

 

[xenou]

Okay. one more question. How have you set up your accounts?

(I'm asking a lot of questions as there's probably more than one way to go about this. So we need to know what your strategy is).

Also - are you using double-entry accounting or not?

 

[Montez659]

Not sure what you are wanting to know as how I set up my accounts, but I'll give a stab at it. Of course I have account name, short acct name, last four digits for acct number, archive (in use or closed), type (checking, savings, credit card).

As far as double entry, no I didn't really think about that at all, and it might be smart move. I was planning on just having the balance updated directly in the accounts table, but I know that is not a great way of doing that. I suppose double entry would solve that problem.

Btw, as far as account type is concerned, I want to in the future be able to enter a credit card transaction and then have that automatically update the future credits/debits outlay. But this isn't super important right now.Basically, I will have on my opening screen a listbox that will contain all of my unpaid or yet to be received bills/paychecks. I would then be able to click on one to pull that info into the transaction screen and run the transaction.

Ask any more questions you want! They help me think through my design better!

 

[xenou]

Okay. Well. There's more than one way to go about this. By double entry accounting, I mean essentially debits/credits - a debit to Groceries -- $100 -- is a credit to Cash -- $100. Thus one entry accounts for both the recording of the expense and the consumption of cash (and, by the way, maintains an even "balance" on both sides of the ledger, debits and credits, which is of course the spark of genius behind the invention of this system of accounting).

My opinion is that the budget table should be separate from the transaction table. You simple record your transactions as they occur. Separately, you build your budget. The transaction table is made up of many individual transactions per month - maybe this is one payment per month (rent) or several (auto fuel) or many (food). It doesn't matter. Each transaction is entered into your system with the appropriate account ID.

The budget is different. It is one amount per period. Even if its food, you budget a single amount per month. The budget also is set up by account ID. To compare budget to actual is then a simple matter: total up the actual transactions by account by month, and compare to the budgeted amount for the same account in the same period (or perhaps, year to date).

So I don't think you actually need to tie transaction entries to the budget tables at all. You only need to write the query/report that compares them by account.

A wrinkle is that, of course, you want to roll up accounts that relate to each other. I use about 50 accounts for my home budgeting. They are more granular (fuel, license and fees, maintenance and repair, insurance are distinct "vehicle" related accounts). But these and others roll up into about 8 or 9 major categories (income, food and groceries, vehicle fuel and maintenance, rent and utilities, household expenses, and so on). In my work I just group the accounts in an "Account Group" table or "Account Class" table. So I can run my queries through this filter and get grouped values rather than distinct account values (a "higher level" view, so to speak). My budget is still created at the account level, but I review it with subtotals by category to get a more general picture of the major elements of my spending (I suppose I could even pie chart it as seen here http://www.wisebread.com/refactor-your-budget-categories)

Okay, so that's my off the top of my head approach. I think it's best to keep things discrete and separate - keep the transactions as one thing, and the budget as another. The chart of accounts is what ties them together when you do your end of the month review (or anytime you want to see budget vs. actual). I think that makes it simpler but still very effective.

 

[Montez659]

So I don't think you actually need to tie transaction entries to the budget tables at all. You only need to write the query/report that compares them by account.

I am not sure I am following you on this point, although everything else looks great. Would you include the budgetID with the transaction? It almost sounds as if you are saying not to. If that is the case, how would you know when running reports what transactions are tied to which budget items?

I think I am missing something here.