GPL (General Public License - http://www.fsf.org/copyleft/gpl.html ) --here are some open-source accounting software projects: Dig into these, for lots of good technical material.. For example, Linux Kontor has thousands of HTML documentation pages in the .tar file which you can download.

Miscellaneous interesting accounting database designs will turn up in any web search.

If you search the internet for "Chart of Accounts" or other general terms you will quickly find that most major universities keep their whole accounting manual (if not the whole data dictionary) online

Bottom line, the best ledger is a web ledger, and the best webledger is one linked to a shared, public transaction log or repository on the internet: http://www.gldialtone.com/STR.htm

But if you're building a standalone or LAN-based accounting system, I prefer a flat ledger table (rather than parent/child tables) as repository for accounting transactions. There is, among all the diverse economic exchanges in the economy, one irreducible common denominator: a value of money, with its accompanying identifiers or codes. Sets of rows having debits equal to credits are a proven model for representation of the monetary component of these transactions. It is outside the scope of a general ledger to model the physical, logistical, or other aspects of human interactions. Accounting modellers must endeavor to avoid getting sucked into the whole of enterprise software development. The scope of financial accounting is the expression of GAAP, i.e. the classification of transactions into discrete categories enumerated in charts of accounts. If management accounting or tax can be delivered by financial accounting structures more economically competitive than the business systems guys, that's fine but it's not General Ledger. It's multipurpose data structures, and all the textbook warnings apply.

A multipurpose data structure (serving management and operational needs as well as financial reporting), a table would have fields for all the main attributes that are important to the business, and the table would contain rows sufficient to represent the nature of each sale or expense or other transaction. That's a lot of columns and rows-- but 17 Gig hard disks around $100. And the simple table is an instant data warehouse accessible by any low-end reporting tool.

This flat Transaction table could support a business by itself. In other words, you could build specialized "Journal Entry" screens for invoices, purchases, project cost, etc. posting directly into the GL table. Or, separate tables could be added for Sales Journal, Purchase Journal, Payroll Journal, etc. as the needs become more sophisticated. (These journals or sub-ledgers could be posted to the GL Transaction table either in detail, or summarized)

In the ideal system, all of the codes (customer, vendor, etc.) are user-definable so that users can implement all lowercase alpha mnemonics if desired, and the codes can be changed or merged i.e. they're not native key fields in the database.

ChartOfAccts --- 10/31/99 -- attempts to manage slowly changing dimensions
LedgerID	Long	4	Unique Primary Key, and Foreign Key, Connects this COA table with its transaction (TRX) table
AcctCode	Text	11	Unique Primary Key, GL Account Code (same as the User sees)
AcctCodeVer	Text	2	2-digit Version of the Account Code (used when the GLAC has been modified)
AcctType	Long	4	FK into AcctTypes table-such as Asset/Liab/Rev/Exp and sub-hierarchies
CoreAcctID	Text	13	FK into CoreAccts table- the pure, root account code omitting any additional segments
AcctName	Text	25	Short form name of this version of the Account Code for some GUI and reports.
AcctNameLong	Text	40	Long, full name of the Account
AdjustingAcctID	Long	4	Adjusting account (e.g. your Sales account would have this field pointing to Sales discounts)
SegmentMapID	Long	4	for Segmented Account Codes--what format or Mask ID is used for this COA.
InactiveLocked	YesNo	1	Flag to Prevent creation of new transactions with this account
DateFirstUsed	Date	8	Tells the adminstrator the first date of usage in the associated TRX table
DateLastUsed	Date	8	Tells the adminstrator the last date of usage in the associated TRX table
PeriodEffectiveFrom	Text	8	Fiscal period of usage in the associated TRX table-enables changing AcctCodes over time
PeriodEffectiveTo	Text	8	Fiscal period of usage in the associated TRX table-enables changing AcctCodes over time
SuccessorAcct	Text	11	For Locked accts: The new account code which is being used for the same types of transactions.
AcctMapping1	Long	4	FK into 2nd COA such as State/StateTax, for reporting
AcctMapping2	Long	4	FK into 3rd COA such as ParentCompany COA, for rollup
AcctMapping3	Long	4	FK into 4nd COA such as Merged or Spinoff Company facilitates business combinations.

Note that the Chart of Accounts table is a "dimension" table, and the GL is the "fact" table within a star schema. We are breaking the rules for large systems. We are doing OLTP into this structure under an assumption of few concurrent users, reliance upon ever improving database engines, correct application logic, and ever increasing hardware and network performance.

Note that the Chart itself has attributes whose values are constrained to values contained in further dimension tables. (The CoreAccounts Table, Account Types table, the ParentCompanyCOA for example)

I have not depicted any other dimension tables associated with the GL table. However, obviously there will be a Customer table, Vendor table, Product table and so forth. Each of these tables can usefully and beneficially have a few dimensions (customer type, for example.) That is the meaning of a Star Schema in a General Ledger.

I am trying to stir up interest in ecommerce over distributed networks. Specifically I have drafted an early spec. for an ecommerce system that would run on any file system such as a freenet, publius, mojonation etc.at http://www.gldialtone.com/FBWspec.htm

Accounting System design - Tables, Fields and Objects

Home