2 Replies Latest reply on Jun 12, 2010 11:24 AM by eibcga

    Data modelling - Normalizationc

    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)

       

      ---


        • 1. Re: Data modelling - Normalizationc
          comment_1

          I believe there are two possible approaches:

           

           

          1. A single transaction record, with fields for:

          * TransactionID
          * CreditAccountID
          * DebitAccountID
          * CreditAmount
          * cDebitAmount (Calculation; = -CreditAmount)

          This is easy to implement - but you cannot produce a report for more that one account at a time.


          2. The second approach is more or less what you described, except the amount goes into the parent transaction record; the two child records hold only the AccountID and the type (credit or debit). Note that this assumes EXACTLY two accounts per transaction.





          • 2. Re: Data modelling - Normalizationc
            eibcga

            Thanks very much.  Option 2 — In my case, I can, and do, have many transactions which have more than two line items (aka compound entries, e.g., record a paycheck on which many amounts affect many different types of accounts like deductions, taxes, etc.), so I think the Amount field would need to stay in the TransLineItems table, since each line item will have a different amount.

             

            I suppose one can go too far with normalization, especially since, again in my case, the number of accounts and contacts I will be using on any given transaction (i.e., the number of records in the Accounts table or Contacts table) will remain the same and I'm sure not going to be adding many new records for these in the future.

             

            Thanks again very much.  This is a great learning experience for me and you have been most helpful and given me more ideas to consider in the future.