Personal Monthly Budget DB Design (1 Viewer)

[ledgerr.rob]

Hello All,

I'm trying my hand at developing a personal database for my wife and I. This would be a monthly comparison of projected expenses and actual costs.

Workflow
At the beginning of the month enter my projected expenses. Each expense would be assigned a general 'category' and a more specific 'item.' Throughout the month we would enter our individual actual costs. At the end of the month I would like a comparison of the those figures, whether we were under or over budget.

I would like to keep these separated by account owner (ie 1.wife, 2. me)

Design
tblAccount
AccountID-Autonumber(pk)
Account-Text

tblCategories
CategoryId-Autonumber(pk)
Category-Text

tblItem
ItemId-Autonumber(pk)
Item-Text

tblProjected
ProjectedID-Autonumber(pk)
Date-Date/Time
Account-Number(fk)
CategoryID-number(fk)
ItemID-number(fk)
Amount-number/currency

tblActual
ActualID-Autonumber(pk)
Date-Date/Time
Account-Number(fk)
CategoryID-Number(fk)
ItemID-Number(fk)
Amount-number/currency
Description-text

Querries
Not to be jumping ahead too much as I only have this on paper, but I think I can summarize the multiple entries of the actual costs by Category and Item. Then I think I can perform a subtraction from the projected expenses by date range(desired month). But how can i ensure that the subtraction/comparison is performed on equal category/item "pairs"?

For Example
Projected Expenses
Food-Groceries: $200
Food-Dining Out: $75

Actual Expense (Summarized Entries)
Food-Groceries: $225
Food-Dining Out: $100

Balance
Food-Groceries ($-25)
Food-Dining Out ($-25)
My question here is I believe in order to do this, the table sizes/schema have to be matching. I could and probably will have costs not foreseen at the beginning of the month. Is this the type of situation that can create a cartession product?

I've just been doing some microsoft training videos and have an old MS Access 97 book that i've been going through for a couple weeks thinking about this.

Thought i'd run this by some folks before i took off down a wrong path too far. I appreciate any comments you would have on any of this.

Thank you,
rob

 

[RainLover]

A quick look at this shows that you have a good foundation.

Suggest that you create your Tables then setup your Relationships.

It is the Relationship Window that will highlight the Good and Bad parts of the design.

Then post a copy of your new Database (In 2003 as we don't all drive the latest cars). Make sure it is the Database not just some pretty pictures.

Hope to hear from you soon. You should be able to do this in 10-15 Minutes.

 

[RainLover]

Is this the type of situation that can create a Cartesian product?

I am not familiar with a Cartesian Product. I am Familiar with a Cartesian Join and you should not be looking at this.

I am also not sure if we are on the same line of thought.

Try the KISS system.

 

[MarkK]

I would combine these two tables, since they contain data of exactly the same structure that differs only by status.

tblProjected
ProjectedID-Autonumber(pk)
Date-Date/Time
Account-Number(fk)
CategoryID-number(fk)
ItemID-number(fk)
Amount-number/currency

tblActual
ActualID-Autonumber(pk)
Date-Date/Time
Account-Number(fk)
CategoryID-Number(fk)
ItemID-Number(fk)
Amount-number/currency
Description-text

Introduce a single field to distinguish types....

tblLedger
LedgerID-Autonumber(pk)
Date-Date/Time
Account-Number(fk)
CategoryID-Number(fk)
ItemID-Number(fk)
Amount-number/currency
Description-text
[B][COLOR="DarkRed"]IsProjected (Boolean)[/COLOR][/B]

 

[ledgerr.rob]

@Rainlover
I'm doin my best to KISS it. As for the Cartesian thing it was just something that i stumbled across that wasn't in full understanding about in all honesty. I'm not really considering it for now. I'm sitting down right now to 'build' this thing to post.

@lagbolt
I like that idea alot. It reduces a table. I'll try that and hopefully my mind gains an understanding of how to use that model as i go.

Thanks for the advice everyone.

 

[ledgerr.rob]

I've attached a copy of the database in v.2003. Included are some account names, categories, and some items. Up to this point things are making sense.

I guess my next question is, now what? lol Do i put some test values into the thing and start playing around?

 

[RainLover]

Comments

Date_1 is not very descriptive. I think you can find better.
Formating in Tables. I leave this to Forms. There are times that you may wish to display data differently on one form to another.

Neither of the above two are wrong, I just don't like.

A Category has Many Types., or Many types can belong to one Category. Your design does not reflect this. Delete Category From tblLedger and put it in tblItems.

I adjusted your workings. See attached.

 

[ledgerr.rob]

Thanks for re-working the structure, appreciated. I do have a question however. When i open tblItems it seems to be pairing the items and categories together. I'm sure this isn't what is happening, rather just the way I'm perceiving it.

If i brought this structure into a form, say a combo box, would it only allow me to select one of the 'pairings?' I hope, at least i think i hope to select a category and then independently select an item.

Your description of one category having many items could be further expanded by adding that an item may have many categories. I think this would present a many-to-many relationship. I know to use a junction table with a many to many, just not exactly sure how to set up the query to link the data from the different tables.

regards
rob

 

[RainLover]

I changed the Items and Categories back the way they were.

A Join table would be the Primary Key (Autonumber) and Foreign keys CaregoryID and ItemsID.

Then add a Foreign key to the Table you want to link to.

Not sure where you are heading so I can only hope this helps.

Post back with any issue.