1

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.

Improve this question

3 Answers 3

Reset to default
1

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.

Improve this answer
0

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.

Improve this answer
4
  • Thanks for the answer, but the problem I have with this idea is there is other information associated with the transaction that is not split like Date, Tags, Comments, AccountId etc... Commented Oct 30, 2014 at 16:37
  • Also I don't really understand the purpose of "SplitLineID". This looks like it's used to store the order of the split amounts? I don't think I care about the order though? Commented Oct 30, 2014 at 18:39
  • 1
    You could put the other information (Date, Tags etc) into a TransactionHeader table, and the table I've described would be a TransactionDetails table. And thinking about your other comment, yes, you won't need the SplitLineID field unless you want to keep the items in order. Commented Oct 31, 2014 at 9:54
  • Wouldn't this be exactly the same as the second example in my original question then? Not that that is necessarily bad, as I was leaning toward that option. Commented Oct 31, 2014 at 10:17
-2

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).

Improve this answer
1
  • 1
    this doesn't seem to add anything substantial over points made and explained in prior answer that was posted few months ago Commented Apr 18, 2015 at 9:50

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.