Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2003

    Question Design for managing financial account transactions

    I have a PostgreSQL database that maintains my checking, credit card,
    and mutual fund accounts. It's been working fine, but I'm not happy
    with part of the design.

    Here is a description of the relevant fields from the relevant tables:

    - account_type_id (primary key)

    - account_id (primary key)
    - account_type_id (foreign key = account_types.account_type_id)
    - account_type_name

    - action_id (primary key)
    - account_type_id (foreign key = account_types.account_type_id)
    - multiplier (-1 or 1, see below)

    - trans_id (primary key)
    - account_id (foreign key = accounts.account_id)
    - action_id (foreign key = actions.account_type_id)
    - amount (positive number)

    The actions.multiplier field is -1 or 1 depending on how the
    particular action affects the balance for the given account type. For
    example, the actions PURCHASE, CHECK, and WITHDRAWAL have a multiplier
    of -1 for CHECKING account types since they debit from the account,
    while DEPOSIT, REFUND, and INTEREST have a multiplier of 1 since they
    add to the account. On the other hand, a PURCHASE or INTEREST for a
    CREDIT_CARD account has a multiplier of 1, since they increase the
    balance, and PAYMENT and REFUND have a multiplier of -1.

    finances=# select * from account_types ;
    account_type_id account_type_name account_type_class
    --------------- ----------------- ------------------
    1 Checking Accounts asset
    2 Credit Cards credit
    3 Mutual Funds asset

    finances=# select * from actions;
    action_id multiplier description
    --------- ---------- -------------------------
    101 1 Automated Credit
    102 1 Deposit
    103 1 Refund
    104 1 Interest
    105 -1 Purchase
    106 -1 Check
    107 -1 Withdrawal
    108 -1 Automated Debit
    109 -1 Miscellaneous Banking Fee
    201 1 Purchase
    202 1 Interest
    203 -1 Refund
    204 -1 Payment
    301 1 Share Purchase
    302 -1 Check

    Notice that the action_id encodes the account_type_id. I don't really
    like this, and I can't think of a better way to handle this.
    Diagramming out the relationships shows an obvious cycle of
    relationships, though I don't know if this is okay from a db theory

    I also don't like having a multiplier as it forces a join whenever I
    want to calculate a balance. I have view for that:

    SELECT t.account_id, SUM(t.amount * a.multiplier) AS balance
    FROM transactions t LEFT JOIN actions a USING (action_id)
    GROUP BY t.account_id;

    but it still seems wrong from a theoretical perspective.

    Can anyone see how this design can be improved?

  2. #2
    Join Date
    Sep 2002

    Re: Design for managing financial account transactions

    My preference would be to hold the transactions.amount as a positive or negative value as appropriate for the action. You would have to use a trigger to enforce the use of the appropriate sign (or to convert positive to negative where appropriate).

    With the current design, I would perhaps create a view at the transaction level rather than account level:

    CREATE OR REPLACE VIEW transactions_view AS
    SELECT t.trans_id, t.account_id, t.action_id, t.amount * a.multiplier AS amount
    FROM transactions t LEFT JOIN actions a USING (action_id);

    Now you can use this in any query that needs the signed amounts, whether it is summed to account level or not.

  3. #3
    Join Date
    May 2002
    Florida, USA
    I have to agree with andrewst. The idea of using a multiplier seems a little unusual.

    I don't know about your specific needs, but often in accounting/transactional situations, transactions are broken down into sub-actions, such as fees assessed, interest, commissions, etc...

    In that case, it makes sense to see a transaction as the overall unit, while you have a separate "postings" table with a many-to-one relationship with the transactions table, which has the actual discrete monetary movements to different accounts to cover the commissions, taxes, etc....

    You might want to consider a basic double-entry approach, with a "cash book" (it's not hard). Here is an excellent introductory article to this:

Posting Permissions

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