what design to split bank transactions into more detail? (in budget database)

[Greg H]

Current Design:
* I have a budget/finance tracking database I’m building
* It has a BANK_TRANSACTIONS table where I load in transactions from various bank accounts
* It there has an ALLOCATIONS table to allocation a bank transaction to a business area (e.g. allocate the phone bill item 50% to “personal” and 50% to “work"

Question: What would be the best database design approach to be able to breakout a bank transaction into smaller items? For example there may have been really two (2) items purchased and turn up in a single bank account. I still want to maintain the correctness of imported bank transactions themselves.

For example some ideas that come to mind:

a) add new detailed transactions to BANK_TRANSACTIONS for the detail, with a new column “REPLACED” so the the original bank transaction is there but can be marked as “replaced” so it doesn’t get used in queries/reports. Then another RELATIONS table to relate the new detailed record to the parent original bank transactions. Not sure if this would be considered good design or not

b) have a separate table for BUSINESS_TRANSACTIONS so the detailed transaction go here. But then 95% of the items in the BANK_TRANSACTIONS would just need to be duplicated in the BUSINESS_TRANSACTION table??

Other ideas???

In terms of usage/output would like have the concept of being able to show:
a) maintain valid true bank transactions that are valid, as it is from there you can see your overall bank balance (across multiple accounts) across time,
b) in terms of reporting for expense categories / taxable items etc need the detailed BUSINESS_TRANSACTION so to speak...