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.