Hi GNU team, We have been looking for an open source accounting system to connect to, to provide additional functionality we would rather not have to code ourselves (reconciliation, all the reporting, etc) as we have a system producing invoices, transactions and so on, using double-entry principles.
We are considering using a small middleware component to map our tables and your SQL tables, and after investigating the DB structure this seems pretty straight forward to do. This component would double up as suitable for mapping to any other accounting system in the future if our customer(s) require us to connect to anything else - but for small business situations, GNU seems perfectly suitable. We would rather not re-invent the wheel. We considered a workflow something along the lines of:- Our system -> Create new invoice -> automatically generate commission -> create the various transactions required (automatic journal posting). Our system would create a unique invoice ref and then create a sync job, ready to copy into the gnucash database - generating the GUID's and following the data structure to ensure data consistency. After a quick review, these are the initial problems we would need to overcome:- 1. Not loading entire dataset at load of application. Our system is some instances generates a significant number of invoices, with millions of individual transaction postings - we can't load the entire dataset into memory every time someone opens the application. It must read from the database in real time and "JIT" load data from the MySQL database. I believe we may be able to achieve this with some sort of DAL which would sit in front of GNU or use the MySQL client library and change the code to query the data - without further investigating I am not sure of the potential work required here. Please feel free to throw your thoughts and ideas across. 2. Not saving entire dataset on-save, but only the changes made, in real time. 3. Adding support for the MySQL client library for the above and generating the queries needed to read and write the necessary data? 3. Adding foreign key constraints to the database to avoid data corruption (should be simple to do) 4. Making use of MySQL triggers to create transaction safe auto-incremented numbering for invoices/credit notes, etc 5. Support creation of transactions from gnu back to our system, not just from our system to gnu Other notes: We noticed the use of GUID frequently being used. This should make our lives easier regarding syncing. It should be possible to add a "last_updated" column in each database table and then use MySQL triggers to update this on change. In our applications we always use a "date_created" + "date_modified" col for every table, with full datetime info stored to allow us to track changes. In theory we could implement this without touching gnu code and benefit from this additional information in the case of multiple writes. Why would we do this?: Due to the nature of our application (typically tens of thousands of transactions a month per instance), we have created a bespoke highly automated approach to accounting which avoids any manual posting for day to day working. We never set out to create an alternative to Quickbooks/Sage - our accounting module was simply created as a means to an end. However our customers are so fed up with Quickbooks and Sage (and their own legacy in-house systems dating back 30+ years in some instances) they are piling on the pressure for us to further develop our accounting module so they can avoid using said packages. When investigating their business requirement it turns out we would need to re-create a lot of the functionality found in gnucash/qb/sage (reconsolidates, reporting, expenses, etc) - which we would rather avoid. Only accountants in our customer's organisations require this data and they will be happy to use gnucash to access this additional information. General viewing of invoices and transaction data can continue to happen in our own system via a web interface for other users. Catch 22; do we invest the time branching gnucash and creating an abstraction layer to MySQL to allow us to have a multi-user concurrent active-active environment, or do we start developing our own platform to replicate all the years of hard work which have been invested in an existing solution? I'm posting this topic hoping for gnu dev's to leave their thoughts on what might await us if we embark on the branch -> develop route! Thanks for listening! -- View this message in context: http://gnucash.1415818.n4.nabble.com/Considering-a-fork-for-implementing-MySQL-C-library-multi-user-thoughts-tp4674947.html Sent from the GnuCash - Dev mailing list archive at Nabble.com. _______________________________________________ gnucash-devel mailing list [email protected] https://lists.gnucash.org/mailman/listinfo/gnucash-devel
