William, I am afraid you are probably so far ahead of me that I can't understand your response.

Let me tell you this is basically an application used to maintain a bank.

I am adding some new functionality that the table design does not lend itself to very well. Original design was done in a flurry (nothing new there) so I have some real problems with creating reversing transactions. For the past month I have revisited every aspect of the app in an attempt to bring all tables to 3rd normal form.

I have been basically stuck for 3 days with this current issue.

Basically there are accounts, one account per person whose account is being maintained.

There are various reasons for crediting accounts (deposits, etc) and various reasons for debiting accounts (purchases from vendors, etc).

I should tell you that I am uneasy about Account_Txns. As much as I like some aspects of the table, it just does not seem correct.

It is much more complex but here is the basic design:

Accounts
cid (surrogate/primary key)
cAccounts_Name
blah...blah...blah...

Account_Txns
cid (surrogate/primary key)
cAccounts_Id (FK into Accounts.cid)
cTxn_Type (values such as "DEPOSIT", "PAYOUT", "PURCHASE-PAYMENT", "TRANSFER-IN", "TRANSFER-OUT")
cUserId
tTxnDateTime
blah...blah...blah...

Deposits
cid (surrogate/primary key)
cAccount_Txns_Id (FK into Account_Txns.cid)
cDeposit_Method (values such as "CASH", "CHECK", etc.)
yAmount
blah...blah...blah...

Payouts
cid (surrogate/primary key)
cAccount_Txns_Id (FK into Account_Txns.cid)
cCheck_Number
yAmount
blah...blah...blah...

Purchases (related to Accounts not Account_Txns)
cid (surrogate/primary key)
cAccounts_Id (FK into Accounts.cid)
cProducts_Id (related to Products table)
blah...blah...blah...

Purchase_payments
cid (surrogate/primary key)
cAccount_Txns_Id (FK into Account_Txns.cid)
yAmount
cPurchases_Id (FK into Purchases.cid)
blah...blah...blah...

The 1st thing I need to do is ensure that Account_Txns is logical/correct design wise.

The other way of doing this would be to have Deposits, Purchase_Payments, Payouts related to Accounts rather than Account_txns. Be aware that I will have Adjustments and Refunds for just about every table as well. I am woriied if I do it without Account_txns, I will have so many joins it will be impossible. In fact I could have UNIONS rather than joins which seems even worse.

Let's say you just want to present a view/grid containing all the transactions for an account. If not for Account_txns, you would basically have to UNION every table that contains a Purchase_Payment, Deposit, Payout and even Purchaser_Payment_Refunds, Deposit_Adjustments and Payout_Voids, etc to the view. That just seems almost undoable to me.

I had better stop here. Man, I would sure appreciate someone making sure I am on the right path with Account_txns. If not the whole design falls apart.

Thanks,
John