0

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.

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.

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.

  • 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... – Eric Oct 30 '14 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? – Eric Oct 30 '14 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. – Dan F Oct 31 '14 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. – Eric Oct 31 '14 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).

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.