Man, Do I need help. I have been redesigning (hoping to improve) a database that I developed. I have been at it for a month and still am unsure about some basic aspects of my design. Even if it means hiring someone to help, I am drowning fast.

Brief description:
Sort of like software that would be used at a bank.
This needs to be fully transactional, i.e. every creation of, or change to a previously created transaction, must result in a separate transaction. I export all transactions not previously exported, to a different system, on demand.
Example: A deposit is created for an account. That deposit transaction is exported. If a change is required to the original deposit transaction, a new transaction must exist that can also be exported.

There are 2 additional fundamental requirements:
1. Users to be able to see all transactions for an account, in datetime order.
2. Users should able to view any original transaction along with any adjustments to that transaction.

This is what I have. I use surrogate keys:

Accounts - 1 rec per account. Information about the account.
cId (surrogate/primary key)
blah...blah...blah...

Account_Txns - 1 rec per transaction per account.
cId (surrogate/primary key)
cAccounts_Id (points to cid in Accounts)
cTxn_Type - There are Deposits, Purchases, Checks written, Deposit Adjustments, Purchase Refunds and Purchase Returns, Voided Checks, etc. basically many transactions and many adjusting transactions.
tTxn_DateTime
cTxn_UserId
blah...blah...blah...

Basically there is a table for Deposits, one for Deposit_Adjustments, one for Purchases, one for Purchase_Refunds, Purchase_Returns, Checks_Written, Voided_Checks, etc., each of which contains info specificto that type of transaction. Each transaction in these tables has apointer to the releated Account_txns record. I believe that is 1 to 1 and that worries me.

My quandry: Account_Txns has me totally perplexed. Is it in 3rd normal form? Should it really exist or should Deposits, Purchases, Checks Written, Deposit_Adjustments, etc be related directly to Accounts and Account_Txns is just a view by unioning all the tables. This is so basic to the design I know I must get it right. Unions are difficult (at least for me) so perhaps Account_Txns is a compromise. If so I have to know it will "be ok".

Thanks,
John Cook
Bulletproof Software

if you want more info, search for the terms "subtype and supertype"

i would always minimize database complexity while at the same time minimizing application logic complexity

if you have separate tables for separate transaction types, your queries are more complex to get all subtype data, but this design may be better suited to processing certain types separately

if it were me, i would just declare the extra columns in Account_Txns and not have separate tables

with one table, some columns may be null, and this makes some people uneasy, but not me

Thanks Rudy. I am a 1 man shop and it is tuff with noone to bounce ideas off. I will search for the terms you stated.
Thanks again,
John