Budget Planner SQL Diagram

Introduction

One of my major software projects includes the Budget Planner budgeting software. This was designed using Python's Tkinter package. One of the major goals I have for this project is database integration.

Getting out of a Pickle

Currently the project utilizes a combination of dictionaries and lists to store data while the program is running. This data is then saved manually to a file using python's pickle package.

This set-up has a few benefits and a few negatives. Here are some of the benefits

• Simple built in data types
• Easy to store and load with pickle
• Can store each budget separately in different file locations

Some of the negatives include

• Loads all data at once
• Data queries require extra work
• Hard to upgrade

Moving everything to a SQL database solution will allow views to load only the data needed and will give the programmer access to SQL based queries. So the first two negatives are easily conquered. As for how easy it will be to upgrade the SQL database, that will depend on the update, but should be fairly straightforward if we stick with SQL.

SQL Diagram

As with any project, the first step is to create a blue print detailing how to accomplish the goal. Integration to a SQL database, from built in Python data types and pickle, will require multiple steps. This is partly due to the goal of improving the base layout of the software to match the improved data type.

As a reference, the original data type is a Python dictionary containing

• budget 'type'
• budget 'name'
• the 'current_budget'
• 'order' of budgets
• 'budgets'

The last bullet point being a dictionary itself containing a collection of named budgets. Each named budget contains a list of dictionaries containing

• 'income_categories'
• 'expense_categories'
• 'transactions'

The goal is to create a SQL diagram which captures similar information while improving data storage. Using crow's foot entity relationship notation and the very useful site diagrams.net I was able to put together the following diagram:

The goal of this diagram is to allow for a higher degree of interconnectivity in the data. We build a page table which will be tied to user income, expenses and transactions. Each entry in the page table represents for example a monthly budget. Notice that the income and expense tables are tied to the budget table, however, there are view tables which are connected with the page table. This will allow us to have distinct income and expense budgets for each page while still utilizing a single collection of user defined incomes and expenses. This is in contrast to the old design which did not have a unified source for incomes and expenses.

The goal of the merchant and category tables is similar. We would like to allow the user to be able to call the merchant and category by any name, but add new table entries to the merchant or category table when a new name appears in our transaction table. Including these tables in the final diagram is still being debated since we could simply view this information by creating an SQL view and not add additional tables.

We will discuss the budget, track, and page tables in more detail when we discuss the software's views. Before that, note that the expense table and transaction table both reference themselves. The goal here is to allow for the option to group transactions from the same merchant on the same day together and allow expenses such as food to have a subcategory such as groceries and eat-out.

Note that the diagram is likely to change during development.

Views

The current version of the Budget Planner software has two primary views: a home page view and a budget page view. The home page allows the user to select previously saved budgets. From here the user can click on one of the budgets to see the budget page view. This shows the most recent budget page. From here the user can toggle between pages in this budget.

One objective for future updates is to improve the primary views. Instead of two primary views, with only one primary view for the selected budget the goal is to add additional views. As with the current version I want a home page. Once in a budget, however, I want a few different views. For starters I want a view for the budget which will display the tracks and pages in a minimized form. I want a page view as we have already. I also want a view for each of the tables merchant, income, expense, and category. This will allow quick renaming of categories if needed. I would also like some type of query view which allows the user to make queries such as getting the total amount of money spent at a given merchant over a period of time. This last part is one of the major benefits I hope to get from using SQL.

Conclusion

With the diagram we can see how I hope to restructure my data in the future. The old structure was simpler, however, it lacked a lot of features I intend to build. In particular, the new structure would allows names for merchant, income, expense, and category tables to be tied directly to the budget instead of loosely to a transaction in the transaction table. This will help enforce naming consistency. In addition, this structure helps define the role of the views with respect to how data is being stored. This is a great benefit as it will help build useful views.

Of course this diagram will likely change somewhat in the final version, but it is useful as a blue print for how to put everything together.