2013-04-14 - Budgeter Project part 1

I have finished my C# book and that means it’s time to start the project. For the past couple of years (Since I started working) I’ve been keeping track of my income and expenses in a spreadsheet. It works alright but I want to build something to automate the process a bit and make entering new items easier. Excel is very good at working with numbers but it’s not really aware of what those numbers mean. By building my own program I can have similar functionality but a lot more content aware. For example excel lets me enter negative amounts because they are valid numbers and that’s all it expects, while the program will know that we are separating transactions into debits and credits and therefore negative amounts don’t make sense. The goal of this project will be to create a program with the same functionality (if not more) and view as the spreadsheet but with more validation and less need for direct input.

The first step will be to figure out the data model. This program is going to do a lot data manipulation and storage so it makes sense to start there. In a lot of ways the spreadsheet is going to be very helpful in this as it shows the kind of information that needs saving. It might also cause problems because I haven’t always been consistent in how I set things up so there might be places where decisions have to be made to either bend the data to fit the model or to expand the model to fit the data. I’ve changed the categories of transactions I use a few times so do I go back and convert all transactions to the new categories or add in the capability of marking categories as unused? The problem with building a specialized application like this is figuring out the right number of options to have; too few and it becomes annoying to use, too many and it gets confusing.

These are the table ideas I have right now and they’re going to serve as the starting point for the data model. The next step will be to start translating the spreadsheet information into these hypothetical tables and see where problems arise. From there it’s just a matter of deciding to change the data or the model until everything agrees.

transaction

This table defines the basic tranaction information and links to aditional information about catagories, if the transaction is a bill and if it's paid with a gift card.

id	date	description	amount	catagoryId	isCredit	isCash	billId	giftCardId
INT	DATE	STRING	DECIMAL	INT	BOOL	BOOL	INT	INT

catagory

This table defines the catagories that transactions can be put into. These catagories describle how the information is displayed and which accounts the transaction goes into or out of.

id	name	colour	withSavings	isCredit	inUse
INT	STRING	STRING	BOOL	BOOL	BOOL

budgetType

This table defines the basic information for a budget type.

id	name	standardAmount	startDate	endDate
INT	STRING	DECIMAL	INT	INT

budgetCatagory

This table maps categories to budget types.

id	budgetTypeId	catagoryId
INT	INT	INT

budgetMonth

This table defines an instance of a budget type for a specific month. This allows the amount per month to change without having to remake the entire budget type.

id	typeId	monthId	amount
INT	INT	INT	DECIMAL

month

This table defines specific information about individual months, mainly the starting account values. During the month the transaction values will be subtracted from the start and then the end result will be used to create the start for the next month. If a value is changed in an already finished month it will update the starting values of all months after it.

id	startCheuqing	startSavings	startCash
INT	DECIMAL	DECIMAL	DECIMAL

specialAccounts

This table represents special accounts. Primarily these will be gift cards but in the future they could be used for TFSAs or similar accounts that aren’t used on a daily basis.

id	monthId	name	amount
INT	INT	STRING	DECIMAL

bills

This table is used to define bills that transactions can be marked as. This allows these specific transactions to be tracked.

id	name
INT	STRING

Also just to go against myself I bought an ASP.net book. It does talk about both C# and VB.net though so that could be a nice way of learning visual basic. I still want to get a VB and a python book at some point but this will be good for now. I’ll probably hold off on the ASP book until after I finish this project or at least get it started.