0 Replies Latest reply on Aug 31, 2016 11:27 AM by golife

    Looking for best data schema for double-entry Financial Accounting

    golife

      Sorry, if I am not using always the correct financial terms (English is not my mother tongue).

       

      Question: What is the best data model for Filemaker for double-entry financial accounting?

       

      I developed an accounting system for myself. It also tracks debtors and creditors (with invoices, credit notes,etc.).

       

      One table connected with the financial accounts and the journal I call Postings (or is it "transactions"?). Going by normalization rules, not supporting redundant data, each Posting just has a financial account and a flag whether it is a debit or credit account.

       

      Each journal entry, therefore, has a minimum of two posting records, one for debit, the other for credit. The date, text and other information are contained in the related journal record.

       

      (Side note: An invoice record, for example, can have more than one Journal record (each Journal record consisting of two Postings), since certain groups of items can belong to different financial accounts, so there are more than just two Postings, there can be four or six and so on - always appearing as pairs.)

       

      The flag for debit/credit is mutually exclusive for both records of a pair.

       

      Another way would be to put amount and flag and both financial accounts into one record. The problem now in FileMaker is that in reports (for example when creating a booking report for an involved financial account) I have to see them in different lines and would have to separate them "on the fly" into two different records nevertheless. (I could use executeSQL and a virtual list to display, but also do not know if it is a good solution.)

       

      Variants:

      1) JOURNAL RECORD ---< 2 x POSTING (Debit/Credit flag ; Account id, Amount)  // Here we have always two records for each "transaction".

      2) JOURNAL RECORD ---< 1 x POSTING (Account_Debit_id, Account_Credit_id, Amount) // Here we can not easily create reports.

       

      A third way would be to maintain both: A Posting record with debit and credit account, and use a script to separate this account additionally into two SubPosting records. But that violates normalization rules and is much more difficult to maintain since any change must ensure the changes also are propagated to the "sub" records. Not ideal either.

       

      So, currently I created the following system:

       

      DOCUMENT ---< JOURNAL ---< POSTING (with two separate records for debit and credit)

      This means a document can have one or more Journal entries which can have two or more Posting records (always in pairs).

       

      Question:

      Any experience developing a double entry accounting system in FileMaker? Any ideas? Any existing solutions?

       

      Remarks:

      My system is multi-tenant, supports different financial accounts for different tenants, all records belong to one and only to one tenant, is supporting foreign currencies, multiple users, multiple languages, allows to manage payables and receivables.


      Thanks for reading ). And thank you very much for any reply.
      Roland