Data modelling - Normalizationc
I have gone through the steps to normalizing my data, I think, to turn it into an efficient database to track financial transactions using the double-entry accounting system, and come up with the Entity Relationship Diagram that appears below.
Since any Account can have transactions with many Contacts (i.e, customers and suppliers), and a Contact can have transactions involving many Accounts, I needed a join-table called Transactions, to change the many-to-many relationship into two separate one-to-many relationships.
Am I on the right track, or do I still have more normalization to do?
The problem I'm finding is that for every Transaction, there must be at least two or more occurences of transaction line items (since the first occurence will have an amount which is positive, and the second occurence will have an amount which is negative, the total of the amount field in the transaction line items table for the two or more line items must total to zero, otherwise all the accounts in the database will not "balance" to zero, i.e., debits equals credits for positives and negative amounts respectively). It seems then, in addition to the Transactions table, I need to add another entity called Transaction_LineItems which include the fields: Acct_ID, Name_ID, Trans_No, Amount, and Trans_ID. Any ideas? Thanks.
Acct_ID_pk link to TRANSACTION LINE ITEMS TABLE
Name_ID_pk link to TRANSACTIONS TABLE
Trans_ID_pk link to TRANSACTION LINE ITEMS TABLE
Name_ID link to CONTACTS TABLE
TRANSACTION LINE ITEMS TABLE
Acct_ID link to ACCOUNTS TABLE
Trans_ID link to TRANSACTIONS TABLE
Total_Amount (total should be zero)