Finance Project 2: Data design

The first thing I need to consider for my personal finance project is the data. How will I store it? How will I structure it? It’s one of the less interesting things to consider – it would be far more fun to just jump into writing code and figure it out as I go along – but if I’m going to do this I want to do it properly.

Fundamentally, a personal finance tracker isn’t much more than a list of transactions. Money comes in (rarely) and goes out (often), with each transaction having some metadata to describe it.

So what about credit cards? Does the money go out when I spend on my credit card or when I pay the credit card bill? If the latter, then how do I itemise individual credit card transactions? I need to be able to record transactions against my credit card – both expenditure and repayment – and be able to see that reflected in the totals. Paying off my credit card bill doesn’t affect my net worth – it reduces the amount of cash in my current account but also reduces my debt by the same amount.

This leads nicely on to the next question. Should I bother with double entry bookkeeping? Would that be overkill for a personal finance application? A single entry would still work just as well with multiple accounts. Double entry bookkeeping has advantages but brings with it a great deal of complexity.

After much consideration I decided that the answer is to use double entry bookkeeping as an underlying mechanism. It’s a pretty time honoured technique, and if I’m going to do this thing, why not do it properly?

That said, I’ll hide it as much as possible. As a user I just want to be able to enter the fact that I bought a coffee at Starbucks – I don’t need to know that this is represented by a credit on my credit card account and a debit on the “Coffee” expense account.

Credits and debits in double entry bookkeeping are often counter intuitive…

A bit of background. After graduating, I spent 18 months training to be a certified management accountant. I should know this stuff. The trouble is I found the computer systems I was using for the accounts far more interesting than the actual numbers, and certainly more interesting than the accounting theory. I escaped into computer programming and the rest is history – including my knowledge of accounting. I had to do some serious revision. All of the examples I could find describe business accounts, and I will make one or two adjustments that make sense for personal finance.

For example, I will treat all purchases of things, with the possible exception of my car, as expenses regardless of whether there is any likelihood that they might have resale value. If I buy a phone, I might well trade it in for another phone some time later, but I’m not going to bother to treat it as an asset. Figuring out the depreciation would be unnecessarily complicated. If I do end up selling something, I’ll treat as a kind of income.

I will also allow entries to be edited. Technically, in a proper double entry system, errors should be corrected by a journal entry reversing or correcting the transactions. Allowing direct editing, though, will make it easier to reconcile back to my bank statements and I can still enforce the double entry rules in the code.

I also need to decide how to structure the accounts – i.e whether to have a complex hierarchy of accounts. Take coffee purchases as an example. One approach would be to have a top level expense account called “Food and drink” with a sub account called “Coffee” and then separate accounts for, say, Starbucks, Cafe Nero, Costa and so on. I think that would be too complicated and not flexible. For a start, “Coffee” doesn’t cover the case where I also have lunch. I think a simpler approach is more practical – one expense account for “Food and drink”, with a field for the vendor and an optional description. That would still allow me to figure out just how much I waste on overpriced coffee without getting bogged down in structural detail.

So how am I going to store this data? I gave some thought to various nosql options, but ultimately there’s no substitute for an SQL database – for the QL more than the S. The structure will be simple, but it’s the query language that will be most useful – SQL is designed and optimised for exactly this sort of use case.

Using SQL also makes it easier to use a framework such as Django, but I’m jumping the gun. There’s still some schema design to be done before I get to the fun bit of coding…

in Finance Project

Add a Comment

Your email address will not be published. All comments will be reviewed.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Related Posts