Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2009
    Posts
    8

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

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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Without knowing a great deal more about both your existing design and your plans to enhance it I can only offer suggestions.

    Add a BUSINESS_TRANSACTIONS table, and make it optional so that not all BANK_TRANSACTIONS require BUSINESS_TRANSACTIONS to provide additional detail.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jan 2009
    Posts
    8
    thanks Pat - so the idea here would be then you create a query to flatten the structure which you then use for reporting? i.e. so a query that could create the union between BANK_TRANSACTIONS and BUSINESS_TRANSACTIONS, but filter out all the bank transactions for which there are one or more business transactions?

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    I was thinking a LEFT JOIN, but depending on what you want to accomplish you might do things differently.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •