I just found FrontAccounting (Not sure how I missed it before, considering how much research I did)! FrontAccounting is a PHP / MySQL bookkeeping system based off OpenAccounting and WebERP.
On first glance, it looks pretty good, and I'm excited to see another project in active development. There are some very nice aspects to FrontAccounting:
- Easy to install
- GPL
- Simple interface
However, I noted something strange about the database model - it has 62 tables!!! That seems a little over the top. PBooks currently as 4 tables (not including user access info):
- accounts
- journalentries
- entryamounts
- general_ledger
Ultimately that won't be enough for all the various permutations a bookkeeping and accounting system can have, and I'm not saying its better to have fewer tables, but I do believe in reducing data to its lowest common denominator.
I'm not sure what is meant by the use of the term "dimension" either, but all in good time. My strategy for analyzing this system will be to remove everything except the basics, then start to dissect it and try and understand the motivation and rationale for the design. Should be fun!!! :-)
I just sent an email to the project coordinator:
I am writing with interest to the FrontAccounting project. I am currently working on a similar project called "PBooks", an aGPL (affero GPL) bookkeeping program. Though the code hasn't been released yet, you can see a demo here:It may make sense for us to join our efforts, but I need to ask about your data model. Is it set in stone? I've taken a different approach in developing PBooks, trying to maintain a minimalist model that focuses on supporting the rules of double-entry accounting. Simply put: debits must equal credits, and assets must equal equity plus liabilities.
Whether or not we combine or merge our code, it will likely make sense for us to communicate about our ideas and development efforts. I look forward to hearing from you!
FrontAccounting Notes
- Use "tags" instead of "dimensions", move the dimension columns from the general ledger table to a transaction metadata table
- Audit trail is nice - but a user_id would be better than the blob data type I think I saw
- Multitenancy is great - meaning the software can manage multiple companies
- Bank accounts are different (at least in a different table - "bank_accounts") than other general ledger accounts ("chart_master") - why? I've seen this before, but never understood it. Even though the "information" is different, the "data" is the same.
- The way the accounts are handled seems confusing - chart_types and chart_classes? Huh?
- Again, there are two tables for seemingly similar data: bank_trans and gl_trans
- What are the doc and tmp directories for?
- Most of the application is based in includes/
- Though the code seems simple enough, I'm a little concerned at how the pieces are connected. It looks like the pages are collections of includes and function calls.
- There is talk of "modules" - are these plugins, in the Wordpress sense?
Front Accounting is a fork of webERP and uses the same data structure ... both applications are much more extensive than just GL. The structure is highly normalised to minimise redundant fields in tables and maximise the efficiency of queries and minimising the size of the data overall. The GL uses 4 tables:
1. chartmaster - the list of GL accounts
2. chartdetails - linked to the chartmaster on the accountcode, but has a primary key of accountcode and periodno - foreign key to the periods table - this table contains the bfwdbalance on the account and the movement on the account for the period. there is one record in the chartdetails for every period/chartmaster record. Also the budget movement for the period. This structure allows a trial balance to be constructed from any range of periods and avoids any period close functionality - the retained earnings figure is recalculated from the sum of profit and loss accounts as at the period from - plus any opening balance in retained earnings.
3. AccountGroups - this allows for chartmaster records to be grouped and sub-grouped (in webERP only) avoiding the lengthy hard coding of accountcodes in the old fashioned way for cost centre reporting. There is one account group to many chartmaster records and there is a foreign key from chartmaster.accountgroup to the accountgroups table primary key
4. gltrans - this is where all the journals are held - the transtype is referenced to the systypes which lists all the transaction types available in webERP. AR receipts - type 12 , goods received, AR invoices - type 10 , AR credit notes - type 11, AP invoices etc etc. Negative amounts represent credits and positive amounts debits. There is extensive narrative allowed on gltrans records. Obviously the gltrans references the chartmaster accountcode and the periods.periodno table.
Hope this is of some use - in your review of FrontAccounting (a fork of OpenAccounting), edgeERP (dormant fork of webERP) and the father of them all webERP
Phil