I’m working on developing a personal finance applications (e.g. Quicken/Mint.com/etc..). I plan to store the data in a SQLite database. I’m stuck trying to determine how I want to represent splits in the database. Each transaction will be assigned a category such as “Food and Dinning” or “Rent”. However, a single transaction may be split across multiple categories. For example a transaction at a grocery store may be split between “Groceries” and “Household”.
Here’s one version of my Transactions table. Transactions that are split would have a Catagory of “Split”.
---Transactions Table (Option 1)---
INTEGER Id
DATE Date
REAL Amount
TEXT Description
TEXT Comment
TEXT Tags
TEXT Category
And here’s my Splits table which identifies how much of each transaction is assigned to specific categories.
---Splits Table (Option 1)---
INTERGER Id
INTEGTER TransactionId
REAL SplitAmount
TEXT Category
The problem I have with this setup is the two tables could potentially get out of sync with one another. If a split is added or removed then the code has to know to switch the transaction category to and from “Split”. Also the total amount of the splits associated with a transaction need to match the actual transaction amount.
Alternatively I was thinking of just treating everything as a split or “TransactionCategory”. So rather then assigning an amount to the transaction the amount is just assumed to be whatever the sum of the “TransactionsCategories” are for each transactions. Transactions that are not split would just end up having a single associated “TransactionCategory” row to identify the amount and category of the transaction. If for some reason a Transaction did not have an associated “TransactionCategory” entry it could just be assumed to be an uncategorized transaction of 0 amount.
---Transactions Table (Option 2)---
INTEGER Id
DATE Date
TEXT Description
TEXT Comment
TEXT Tags
---TransactionCategories Table (Option 2)---
INTERGER Id
INTEGTER TransactionId
REAL Amount
TEXT Category
I’m leaning towards this option as I do not have to keep the two tables in sync, but I’m worried about the overhead of having to join the two tables and take the sum of the TransactionCategories table anytime I need to show a list of transactions and their total amounts.
The table design for a double entry bookkeeping system has been pretty well understood for the last 35 years. You should be able to find hundreds of examples on the web.
You may be able to find a model in the Data Models website.
Double entry bookkeeping is probably overkill for personal finances, but you can probably start with a data model for double entry, and then build an application that makes it look like single entry bookkeeping for your user.
If you don’t understand double entry bookkeeping, maybe you should learn it before venturing into building financial apps for other people.
I’d add two extra integer columns in the Transactions table and get rid of the Splits table completely. Keep the ID column as it is, but call one of the new fields TransactionID, and the other one SplitLine, which will be an integer. The data would look like this:
ID TransactionID SplitLineID Amount Category
1 1 1 12.00 Groceries
2 2 1 20.00 Household
3 3 1 5.00 Groceries
4 3 2 7.00 Books
So here transaction 3 is for a total of $12, split $5 on groceries and $7 on books.
4
Double entry handles split transactions well.
Credit checking 200
and
debit grocery 150
debit household 50
Debits = Credits.
The double entry means recording both debit(s) and credit(s).
1