AnsweredAssumed Answered

Data modelling - Normalizationc

Question asked by eibcga on Jun 11, 2010
Latest reply on Jun 12, 2010 by eibcga

Title

Data modelling - Normalizationc

Post

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.

 

ERD

 

ACCOUNTS TABLE

Acct_ID_pk link to TRANSACTION LINE ITEMS TABLE

Acct_Name

Acct_Type

 

CONTACTS TABLE

Name_ID_pk link to TRANSACTIONS TABLE

Name

Address

Phone

Fax

Email

 

TRANSACTIONS TABLE

Trans_ID_pk link to TRANSACTION LINE ITEMS TABLE

Name_ID link to CONTACTS TABLE

Date

Memo

Quantity

Reference_No

 

TRANSACTION LINE ITEMS TABLE

Trans_Line_No_pk

Acct_ID link to ACCOUNTS TABLE

Trans_ID link to TRANSACTIONS TABLE

Amount

Total_Amount (total should be zero)

 

---


Outcomes