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.
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:
CREATE OR REPLACE VIEW balances AS
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.
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.
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....