John -
ah - a bank .
ok - its a bit different than just a general ledger accounting thingie .
Your account transaction log table - is it possible
to add in a 'thing' that handles reconciliation [ah! ding ! adjustments and refunds] ?
I think thats where you want to go .
Every night, right ? there is posting and reconciliation -
so if you have a 'set' of debits/credits representing a correction [read adjustments and refunds] done on same day or on same posting cycle - you can run it through your 'thing' and create ONE transaction to put into the transaction log table, representing the actualized amount [20-30+0.14+2.33 etc etc] .

I would suggest on each days collection o transactions - you have some preflight process that will check for these types of corrections prior to appending into the transaction table. I'm assuming the transaction table is for POSTED transactions and you are now making a reconciliation for corrections. I can see the difficulty now for making an adjustment/refund transaction - I would assume you would WANT it to show up on the statement as a seperate transaction, but if you are wanting to 'adjust/correct' a transaction itself - I'm thinking you really want an audit table of some type for each adjustment/refund transaction [just in case].

So this 'thing' would be like a funnel INTO the transaction table, handling all reconciliations / corrections each day for an account. You might turn it into some class in a classlibrary, have some temp tables / cursors used for storage and collection, etc etc.
more inline comments below - mondo regards [Bill]
--
>William, I am afraid you are probably so far ahead of me that I can't
understand your response.
I get that a lot - I try to use some variant of English on most occassions.
Perhaps over a beer or 12 with a 1/2 inch stack of greenbar paper we could actually whoop out a design . I enjoy beer, and I always do my first level designs on greenbar [gots cases in the garage]
>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.
>
OK - at this point I'm suggesting you put in yet another process, but I sense it will work for 'todays' transactions only. IF some other day ? if a posted 'fix' to correct something from 4 days ago ? you probably want it to show up on the account holder's statement as a minute/atomic transaction.

>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.
>
One thing I'm not clear on yet - is does ACCOUNT_TXNS table hold the overnight CORRECT postings, historically to date ? IE - is this the table that holds ALL of the transactions that are used for statement generation?

>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.
>
if its possible to add in 1 more column to the account_txns table ?
based on cTxn_Type [add some more types here for adjustment/refund] and
then this NEW column would be an FK , but POINTING back to the PK of the record IN THE SAME TABLE that holds the original transaction.
You'll need some additional processing logic with [gasp] a join, but
it should not be that hard ..
>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